Datetime parameter format from Crystal Reports to SQL Server stored procedure

I am trying to create a report using a SQL Server stored procedure that expects two datetime input parameters. However the stored procedure does not accept the input parameter value selected on prompt screen, generating an error

'Incorrect syntax near keyword CONVERT'.

SQL generated by report is using Convert function by default

EXECUTE [Projects_findings_summary_date]
     CONVERT (DATETIME, '12/01/2017 00:00:00', 120),
     CONVERT (DATETIME, '12/31/2017 12:59:59', 120) 
GO

This code generated the error in SQL Server Management Studio as well. But it works fine if we take the CONVERT function out.

EXECUTE [Projects_findings_summary_date]
     '12/01/2017 00:00:00',
     '12/31/2017 12:59:59'
GO

Any help resolving this issue is appreciated.

We have recently upgraded to Crystal Reports 2016 and SQL Server 2014.

You cannot use convert/cast functions when passing variables to the stored procedure, but you declare the variables before passing it to the proc, do the convert/cast to the variables and then pass the variable to the proc, something like this:

Declare  @FromDate  DATETIME = CONVERT (Datetime,'12/01/2017 00:00:00', 120)
       , @ToDate    DATETIME = CONVERT (Datetime,'12/31/2017 12:59:59', 120);

EXECUTE [Projects_findings_summary_date] @FromDate
                                       , @ToDate
GO

Note

When you are converting a string to DateTime value, the third parameter (Style) in CONVERT function is redundant, DateTime has no style, it uses its standard format (ticks) to store data in SQL Server.

Crystal Reports, I am trying to create a report over Sql Server Stored Proc that expects two datetime input parameters. However the SP does not accept the input� I usually either make the parameter of the stored procedure a VARCHAR representing the datetime, and limit it to a certain format say mm/dd/yyyy, or immediately inside the procedure strip the time out of the parameter before using it. The second option is usually the better option, in my experience.

IN Crystal Report as command type the following

declare @datefrom as datetime = {?datefrom}  -- parameter in CR
declare @dateto as datetime = {?dateto}  -- parameter in CR

EXECUTE [Projects_findings_summary_date] @datefrom, @dateto

--@datefrom and @dateto are datetime variables

You don't need to convert

Datetime parameter format from Crystal Reports to SQL Server , I am trying to create a report using a SQL Server stored procedure that expects two datetime input parameters. However the stored procedure does not accept� I have a CR .rpt file that uses a SQL stored procedure passing 2 datetime parameters and other stuff. The parameters are really dates, but that type was not available when the report was written. I am now moving to Crystal 2011 and MS SQL 2012 which handle the date type. I have redone the SP to expect date types.

Resolved the issue by using SQL OLE DB Native Driver instead of Microsoft OLE DB provider for SQL Server. Convert is not being generated by Crystal report now.

Crystal Reports, I need to use a stored procedure (MSSQL) that has a datetime parameter. When I add it to the report, the parameter shows up nicely, but is there any way that I� Stored procedure parameters behave almost identically to Crystal Reports parameter fields. The stored procedure parameters will appear in the Parameter Fields category of the Field Explorer. You can edit a stored procedure parameter by selecting it and clicking the Edit button, typing CTRL-E, or right-clicking the parameter and choosing Edit

Resolved issue by following the below steps.

1.Installing "MS OLE DB Driver" from link MSOLEDBDRIVER .

2.In stored procedure, replace "DateTime" with "Date" for input parameters.

3.In Crystal Report, make a new connection, select ->OLE DB->"Microsoft OLE DB Driver for SQL" as provider. In "Advanced Information" tab, set "DataTypeCompatibility" = 80.

SQL Stored Procedure to populate Crystal Report, I'm working on a stored procedure to populate a Crystal report. parameters in the stored procedure instead of in Crystalso that the SQL server (rather Apparently SQL doesn't have a plain "date" parameter without a time. The Crystal format for the parameter is uneditable and is set to DateTime, even� Standard Report Creation Wizard will open, from Available Data Sources select My Connection -> [Your SQL Server name or Computer Name] -> Northwind -> Stored Procedure -> GetCustomerOrderDetails;1 and click on > button then Next buttons.

How to pass date parameters to Crystal Reports from a Stored Proc , (I'm using the Crystal32.OCX with VB6, Crystal Reports 7 and SQL Server 7) For reasons unknown, I'm unable to correctly pass to CR two date parameter. dtB = Format$(dtpBegin, "yyyy-mm-dd") & " 00:00:00.000" Even you change it to date format, Crystal still considers it as datetime format. Recommendation is to convert the date prompt in the store procedure to varchar(10) data type, then convert it to date time when Crystal Reports pass the value to SQL.

Date parameter issue in stored procedure(SQL server 2008R2), My report datasource is a stored procedure from SQL server 2008 R2. change it to date format, Crystal still considers it as datetime format. If you have to format on the db side, what you're going to end up with after using the convert function is a varchar, so it won't work to store that in a datetime variable the datetime

Crystal Reports Input Parameter Date Format, The date is currently hardcoded in the sql statement and I would like to SQL Server will cast a string without a time to a datetime and since no time is you want to create a stored proc in Informix and call it from the report� create procedure new_proc (@from_date datetime , @to_date datetime) as create table #temp(date datetime, count int default 0) while(@from_date <= @to_date) begin insert into #temp values('date','(@from_date)') set @from_date = @from_date+1 end--select * from #temp update #temp set count=X.count from(select date , count(1)as count from trade_detail

Comments
  • I am using Crystal reports to read the SP. On connecting the report prompts for value to be selected from a calendar. On execution the report generates the code with CONVERT automatically and generates the error. The workaround I see is creating a string report level variable to pass the values to SP instead of direct user input
  • Your note is wrong. The third parameter (style) is telling SQL Server how to convert the string to datetime. It's true that datetime doesn't have a display format (or style), but the string representation of it does have a style. Omitting the style parameter may result with an error or worst - wrong date. Does 12/01/2017 mean January 12th or December 1st? Only by using the style parameter you can tell SQL Server which one is it, and in this case 120 is the wrong value. It should either be 101 or 103.
  • The SP filters underlying data set based on a timestamp. End users require the ability to input time to see records at a certain point of time. For ex - get list of all today's transactions until 3 PM. We cannot remove the time element from the parameters.
  • you don't need to convert