Datetime parameter format from Crystal Reports to SQL Server stored procedure

Question

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.

Answer 1

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.

Answer 2

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

Answer 3

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.

Answer 4

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.