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


Anonymous said...

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)

Brent V said...

Hi Anonymous,

That is a good tip. Thank you!


Chinde Bayu said...

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

Brent V said...

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,


chinde Bayu said...

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

Brent V said...

chinde Bayu,

Glad to help. Thanks for the Kudos.