Friday, September 25, 2009

Easy way to strip time part in sql date time

Most of the times we wouldn't want the time part of the dates to be stored or retrieved when dealing with only days. But the data type "datetime" in sql always includes the time even though you don't want it. There is no other data type in sql to store or retrieve the date without time. In this case we need to strip the time part from the date and store the remaining in database. To do this favor, I have come up with the below sql code which strips the time part from the date time.

declare @date datetime;
set @date = getdate();
select cast(convert(varchar, @date, 101) as datetime) --Strips time element from the date time

The above code will work like this:
If you have the date time 25/09/2009 12:36:40:654 then this will be converted as
25/09/2009 00:00:00:000

kick it on DotNetKicks.com
Shout it

2 comments:

jamiegs said...

This
CAST(FLOOR( CAST( GETDATE() AS FLOAT)) AS DATETIME)
actually works a bit quicker, there's no string conversion, it's all numeric

Kiran said...

Thanks this was just what i was looking for!