kevin.upshaw 1 Posted May 5, 2012 Report Share Posted May 5, 2012 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 Quote Link to post Share on other sites
Ingolme 1,021 Posted May 6, 2012 Report Share Posted May 6, 2012 I believe Datetime fields are stored in the format YYYY-MM-DD HH:MM:SS Quote Link to post Share on other sites
kevin.upshaw 1 Posted May 6, 2012 Author Report Share Posted May 6, 2012 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 Quote Link to post Share on other sites
kevin.upshaw 1 Posted May 6, 2012 Author Report Share Posted May 6, 2012 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) Quote Link to post Share on other sites
Err 10 Posted May 6, 2012 Report Share Posted May 6, 2012 Try this: SELECT * FROM [order] WHERE orderDateTime LIKE '04_05_2012%' Quote Link to post Share on other sites
kevin.upshaw 1 Posted May 6, 2012 Author Report Share Posted May 6, 2012 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 Quote Link to post Share on other sites
Guest LH91325 Posted May 12, 2012 Report Share Posted May 12, 2012 Another solution might be this: SELECT * FROM [order] WHERE orderDateTime LIKE '%2012%05%04%' Quote Link to post Share on other sites
Ingolme 1,021 Posted May 14, 2012 Report Share Posted May 14, 2012 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% Quote Link to post Share on other sites
Guest LH91325 Posted May 14, 2012 Report Share Posted May 14, 2012 Hey, I didn't know that! Quote Link to post Share on other sites
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.