35

Possible Duplicate:
Get just the Date from grouping in select from DateTime column in SQL Server

How can I get only the Date part of a DateTime ? I'm searching for something like year() function but for the whole date.

Community
  • 1
  • 1
Bigballs
  • 3,729
  • 10
  • 30
  • 27
  • See also: http://stackoverflow.com/questions/133081/most-efficient-way-in-ms-sql-to-get-date-from-datetime and http://stackoverflow.com/questions/2775/whats-the-best-way-to-remove-the-time-portion-of-a-datetime-value-sql-server – Kristen Feb 19 '09 at 20:59

5 Answers5

78

This may also help:

SELECT convert(varchar, getdate(), 100) -- mon dd yyyy hh:mmAM (or PM)
                                        -- Oct  2 2008 11:01AM
SELECT convert(varchar, getdate(), 101) -- mm/dd/yyyy - 10/02/2008                  
SELECT convert(varchar, getdate(), 102) -- yyyy.mm.dd – 2008.10.02           
SELECT convert(varchar, getdate(), 103) -- dd/mm/yyyy
SELECT convert(varchar, getdate(), 104) -- dd.mm.yyyy
SELECT convert(varchar, getdate(), 105) -- dd-mm-yyyy
SELECT convert(varchar, getdate(), 106) -- dd mon yyyy
SELECT convert(varchar, getdate(), 107) -- mon dd, yyyy
SELECT convert(varchar, getdate(), 108) --  hh:mm:ss
SELECT convert(varchar, getdate(), 109) -- mon dd yyyy hh:mm:ss:mmmAM (or PM)
                                        -- Oct  2 2008 11:02:44:013AM   
SELECT convert(varchar, getdate(), 110) -- mm-dd-yyyy
SELECT convert(varchar, getdate(), 111) -- yyyy/mm/dd
SELECT convert(varchar, getdate(), 112) -- yyyymmdd
SELECT convert(varchar, getdate(), 113) -- dd mon yyyy hh:mm:ss:mmm
                                        --  02 Oct 2008 11:02:07:577     
SELECT convert(varchar, getdate(), 114) -- hh:mm:ss:mmm(24h)
SELECT convert(varchar, getdate(), 120) -- yyyy-mm-dd hh:mm:ss(24h)
SELECT convert(varchar, getdate(), 121) --  yyyy-mm-dd hh:mm:ss.mmm
SELECT convert(varchar, getdate(), 126) -- yyyy-mm-ddThh:mm:ss.mmm
                                        --  2008-10-02T10:52:47.513
-- SQL create different date styles with t-sql string functions
SELECT replace(convert(varchar, getdate(), 111), '/', ' ') -- yyyy mm dd
SELECT convert(varchar(7), getdate(), 126)                 -- yyyy-mm
SELECT right(convert(varchar, getdate(), 106), 8)          -- mon yyyy

The Source

Edd
  • 975
  • 1
  • 6
  • 14
33

The solution you want is the one proposed here:

https://stackoverflow.com/a/542802/50776

Basically, you do this:

cast(floor(cast(@dateVariable as float)) as datetime)

There is a function definition in the link which will allow you to consolidate the functionality and call it anywhere (instead of having to remember it) if you wish.

Community
  • 1
  • 1
casperOne
  • 73,706
  • 19
  • 184
  • 253
  • Here is the better answer https://stackoverflow.com/questions/113045/how-to-return-only-the-date-from-a-sql-server-datetime-datatype – Adeem Oct 24 '17 at 11:49
  • When I try to create a stored proc using the sample above (in MSSQL-2012) I get the following error: Msg 529, Level 16, State 2, Procedure xxxx, Line 136 [Batch Start Line 9] Explicit conversion from data type date to float is not allowed. – open-collar May 01 '18 at 07:54
  • If you're using an MSSQL server version 2008 and up, just using cast(@dateVariable as date) may work as well. – RonaldB Nov 16 '18 at 19:47
17

Another nifty way is:

DATEADD(dd, 0, DATEDIFF(dd, 0, [YourDate]))

Which gets the number of days from DAY 0 to YourDate and the adds it to DAY 0 to set the baseline again. This method (or "derivatives" hereof) can be used for a bunch of other date manipulation.

Edit - other date calculations:

First Day of Month:

DATEADD(mm, DATEDIFF(mm, 0, getdate()), 0)

First Day of the Year:

DATEADD(yy, DATEDIFF(yy, 0, getdate()), 0)

First Day of the Quarter:

DATEADD(qq, DATEDIFF(qq, 0, getdate()), 0)

Last Day of Prior Month:

DATEADD(ms, -3, DATEADD(mm, DATEDIFF(mm, 0, getdate()), 0))

Last Day of Current Month:

DATEADD(ms, -3, DATEADD(mm, DATEDIFF(m, 0, getdate()) + 1, 0))

Last Day of Current Year:

DATEADD(ms, -3, DATEADD(yy, DATEDIFF(yy, 0, getdate()) + 1, 0))

First Monday of the Month:

DATEADD(wk, DATEDIFF(wk, 0, DATEADD(dd, 6 - DATEPART(day, getdate()), getdate())), 0)

Edit: True, Joe, it does not add it to DAY 0, it adds 0 (days) to the number of days which basically just converts it back to a datetime.

veggerby
  • 8,940
  • 2
  • 34
  • 43
  • Dateadd(interval, number, date) ... so essentially you are adding 0 to this... cast(datediff(dd, 0, [yourdate]) as datetime) – dotjoe Feb 19 '09 at 20:50
6

We can use this method:

CONVERT(VARCHAR(10), GETDATE(), 120)

Last parameter changes the format to only to get time or date in specific formats.

Peter Lang
  • 54,264
  • 27
  • 148
  • 161
Suraj
  • 61
  • 1
  • 1
2

This may not be as slick as a one-liner, but I use it to perform date manipulation mainly for reports:

DECLARE @Date datetime
SET @Date = GETDATE()

-- Set all time components to zero
SET @Date = DATEADD(ms, -DATEPART(ms, @Date), @Date) -- milliseconds = 0
SET @Date = DATEADD(ss, -DATEPART(ss, @Date), @Date) -- seconds = 0
SET @Date = DATEADD(mi, -DATEPART(mi, @Date), @Date) -- minutes = 0
SET @Date = DATEADD(hh, -DATEPART(hh, @Date), @Date) -- hours = 0

-- Extra manipulation for month and year
SET @Date = DATEADD(dd, -DATEPART(dd, @Date) + 1, @Date) -- day = 1
SET @Date = DATEADD(mm, -DATEPART(mm, @Date) + 1, @Date) -- month = 1

I use this to get hourly, daily, monthly, and yearly dates used for reporting and performance indicators, etc.

Ryan
  • 7,835
  • 2
  • 29
  • 36