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.
Here's another way to do it, which is almost certainly less portable:
ReplyDeleteselect 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.
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.
ReplyDeleteTry THis:
cast(cast(Getdate() as char(11)) as datetime)
-Richard
I usually write like this:
ReplyDeleteselect convert(varchar(10),getdate(),121)
Then you only get the date like:
2008-12-11
/Tobias
CONVERT(VARCHAR,start_dttm,111)
ReplyDeleteConverts
'2009/12/01 11:56:34'
or
'12/01/2009 11:56:34'
into this
'2009-12-01'
CONVERT(DATETIME, FLOOR(CONVERT(INT,GETDATE())))
ReplyDeletecast(cast(getdate() as int) as datetime)
ReplyDeleteI use a quite easy solution:
ReplyDeleteCAST(GETDATE() as DATE)
Convert(Date, Getdate())
ReplyDeletein SQL SERVER 2008, Instead Of Using CAST
Thank you! its a very simple solution. I will make it a scalar function under the database and can reuse it in the future.
ReplyDeleteHi try this..http://codingresolved.com/discussion/62/how-to-get-date-from-datetime-in-sql#Item_2
ReplyDelete