Thursday, November 8, 2007

Query by date (no time) in SQL Server

This is a simple one. This technique can be used to search for rows where the MyDateColumn has the date specified (as a string) and the time doesn't matter. select * from MyTable where Convert(VARCHAR, MyDateColumn, 101) = '10/31/2007' It can also be used in the select statement to format the output of MyDateColumn without the time portion. select Convert(VARCHAR, MyDateColumn, 101) = '10/31/2007' from MyTable

at

6 comments:

  1. AnonymousAugust 18, 2009 at 3:46:00 PM MST

    If you still want to use the date as a datetime for comparison but dont' want the time to throw you off, this is it.

    select Convert(datetime, Convert(VARCHAR, getdate(), 101) , 101)

    ReplyDelete
  2. Brent VAugust 27, 2009 at 3:03:00 PM MST

    Hi Anonymous,

    That is a good tip. Thank you!

    Brent

    ReplyDelete
  3. Chinde BayuOctober 1, 2009 at 10:02:00 AM MST

    i am using Visual studio 2008, i make some program to remote database there but at the table data type its only datetime, and smalldatetime that i can choose to make time only that shows ex. 12:00. is there a way to make it possible???? please help me, anyone

    ReplyDelete
  4. Brent VOctober 1, 2009 at 10:48:00 AM MST

    Hi Chinde Bayu,

    You can always store the time as a varchar (string). You can use a datetime also. You will just want to keep your date part of it to always be the same date. If you are talking about just displaying the time, you can format the date as a string with only the time part.

    I hope that helps,

    Brent

    ReplyDelete
  5. chinde BayuOctober 3, 2009 at 8:44:00 PM MST

    whoa thanks alot brent V, i owe you one. hope with this, my final assignment one step closer

    ReplyDelete
  6. Brent VOctober 6, 2009 at 8:38:00 AM MST

    chinde Bayu,

    Glad to help. Thanks for the Kudos.

    Brent

    ReplyDelete