Thursday, February 16, 2006

Get Only Date from DateTime in T-SQL

T-SQL: Get only Date from DateTime

"SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) "

Result: “2006-02-16 00:00:00.000”

Nice. Short and sweet…I so know I’ll need this and won’t remember where to find it…

I don’t play with T-SQL date and time formating often enough, so each time I do I seem to have to relearn the tricks and gimmicks. (Let’s not get me started on my feelings about date & time formatting in T-SQL, okay?)

If you’re interested in this, make sure you also check out the blog Anatoly references,  Ayende Rahien's Blog [subscribed] and his related T-SQL DateTime post, JustDate: Striping the time part from datetime variables.

 

10 comments:

JP said...

Here's another way to do it, which is almost certainly less portable:

select cast(cast(getdate() as int ) as datetime)

It depends on the fact that, internally, dates are stored as the ole date format, a fractional number of days past some epoch start.

Anonymous said...

Converting a date to an int, then back to a data will result in a whole date (time being dropped), but is not reliable. The reason is that rounding occurs and if the time portion is more than .5 of a day the date you get is for the following day.

Try THis:
cast(cast(Getdate() as char(11)) as datetime)
-Richard

Anonymous said...

I usually write like this:
select convert(varchar(10),getdate(),121)

Then you only get the date like:
2008-12-11

/Tobias

SidCharming said...

CONVERT(VARCHAR,start_dttm,111)

Converts

'2009/12/01 11:56:34'
or
'12/01/2009 11:56:34'

into this
'2009-12-01'

Unknown said...

CONVERT(DATETIME, FLOOR(CONVERT(INT,GETDATE())))

ángel blogger said...

cast(cast(getdate() as int) as datetime)

Matej Kovac said...

I use a quite easy solution:
CAST(GETDATE() as DATE)

Anonymous said...

Convert(Date, Getdate())

in SQL SERVER 2008, Instead Of Using CAST

Anonymous said...

Thank you! its a very simple solution. I will make it a scalar function under the database and can reuse it in the future.

Anonymous said...

Hi try this..http://codingresolved.com/discussion/62/how-to-get-date-from-datetime-in-sql#Item_2