Jump to content
kevin.upshaw

SQL DateTime - SQL Compact Database

Recommended Posts

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: post-95813-0-75391400-1336248495_thumb.png As you can see from the data view there is data applicable in there to my query: post-95813-0-32721200-1336248496_thumb.png Am at a loss. Can anyone help? Thanks. Kevin

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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)

Share this post


Link to post
Share on other sites

Try this:

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
Guest LH91325

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

Share this post


Link to post
Share on other sites
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%

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...

×
×
  • Create New...