SQL DateTime - SQL Compact Database

[kevin.upshaw]

Hi. Wonder if anyone can help. Am trying to retrieve records from my order table from a date inputted by the user. I've done all the SQL on my site up to here - this one is really baffling me through. Query: SELECT * FROM [order] WHERE orderDateTime LIKE '04/05/2012%'; However, it doesn't return any values - see my data types in my table below: As you can see from the data view there is data applicable in there to my query: Am at a loss. Can anyone help? Thanks. Kevin

[Ingolme]

I believe Datetime fields are stored in the format YYYY-MM-DD HH:MM:SS

[kevin.upshaw]

Hi. I have tried the following query: SELECT * FROM [order] WHERE orderDateTime LIKE '%2012-05-04%' Unfortunately that doesn't return anything. However if I run this query: SELECT * FROM [order] WHERE orderDateTime LIKE '%2012%' It pulls back all my orders. Its as soon as I put a "-" or a "/" it just doesn't return any values. Kev

[kevin.upshaw]

I have also tried using the convert function to no joy (to return the date value): SELECT * FROM [order] WHERE CONVERT(NVARCHAR, [orderdatetime], 101) = CONVERT(NVARCHAR, '2012-05-05', 101)

[Err]

Try this:

SELECT * FROM [order] WHERE orderDateTime LIKE '04_05_2012%'

[kevin.upshaw]

Hi. Thanks for your help. I managed to get around it using this code: var extractOrderInformation = "SELECT * FROM [order] WHERE CONVERT(NVARCHAR, orderdatetime, 103) BETWEEN CONVERT(NVARCHAR, @0, 103) AND CONVERT(NVARCHAR, @1, 103)"; Thanks again. Kev

[Guest LH91325]

Another solution might be this: SELECT * FROM [order] WHERE orderDateTime LIKE '%2012%05%04%'

[Ingolme]

Another solution might be this: SELECT * FROM [order] WHERE orderDateTime LIKE '%2012%05%04%'

Remember that you can use the underscore to match exactly one character rather than zero or more: %2012_05_04%

[Guest LH91325]

Hey, I didn't know that!