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.
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
User contributions licensed under CC BY-SA 3.0