Crystal reports Command for date Range in Visual Studio 2012

0

I am editing an application that is made in Visual Studio 2012. I am trying to create a Crystal Report where the user selects a date range. I added a command for my simple SQL Server Query, which runs fine in the SQL Management Studio.

Select customerID, orderID, orderDate
From tblOrders
Where orderDate BETWEEN CONVERT(DATETIME, '01/01/2015 00:00:00', 102) 
                    AND CONVERT(DATETIME, '01/01/2016 11:59:59', 102)

I then created the parameters From and To as dates and substituted my dates:

Select customerID, orderID, orderDate
From tblOrders
Where orderDate BETWEEN CONVERT(DATETIME, '{?From} 00:00:00', 102) 
                        AND     
                        CONVERT(DATETIME, '{?To} 11:59:59', 102)

I get the following error using the same dates:

Failed to retrieve data from the database.
Details: ADO Error Code 0x80040e14
Source Microsoft OLE DB Provider for SQL Server Description: Incorrect syntax near '2015'.
SQL State: 42000
Native Error: 102 [Database Vendor Code 102]

I tried the query without the Convert function, but get the same result.

.net
sql-server
vb.net
visual-studio
crystal-reports
asked on Stack Overflow Mar 28, 2017 by Rick

1 Answer

0

This is not a correct code:

'{?From} 00:00:00'

it can be:

{?From}  & " " & '00:00:00' in formula

And i am wondering why you want to do like that a Parameter with static time?

This is the convertion running as tested.

CONVERT(DATETIME,convert(datetime, CONVERT(varchar,dt,101) + ' 00:00:00',102), 102) 

you can change the dt to {?From} in your crystal report

answered on Stack Overflow Mar 29, 2017 by Vijunav Vastivch • edited Mar 30, 2017 by Vijunav Vastivch

User contributions licensed under CC BY-SA 3.0