Categories
date datetime sql sql-server-2008 tsql

How to return only the Date from a SQL Server DateTime datatype

2066

SELECT GETDATE()

Returns: 2008-09-22 15:24:13.790

I want that date part without the time part: 2008-09-22 00:00:00.000

How can I get that?

9

2759

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date))

for example

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

gives me

2008-09-22 00:00:00.000

Pros:

  • No varchar<->datetime conversions required
  • No need to think about locale

14

  • 63

    +1 Looks like this one is 35% faster than the double convert() method commonly used (which I also have used for years). Nice one.

    – Dane

    Sep 22, 2008 at 4:04

  • 8

    The only downside I can see to your solution is that unless you know what it is doing it is a bit obtuse. Using the double convert method makes your intentions more obvious to futire code maintainers. BTW I have not downvoted you. I think I’ll start using your method too. Thankyou @aku

    Sep 24, 2008 at 8:25

  • 41

    @pilavdzice Setting a datetime to midnight of that day does LEAVE OFF THE TIME. What result are you expecting? The datetime data type cannot have no time at all. I think you are confusing data storage with user presentation. If all you want is a way to show a user a string that has no time portion (not zeroes, just blanks) then you simply want Convert(varchar(30), @Date, 101) or something similar. See SQL Server Books Online • Cast and Convert for more info.

    – ErikE

    Aug 17, 2012 at 22:03


  • 7

    @user1671639 the datetime data type always contains both a date and a time, you can’t sensibly store one without the other – unless you’re using SQL Server 2008, in which case there are also separate ‘date’ and ‘time’ data types. If you use CONVERT() like that, you really want a string for later use, so you’ll be stuck doing it like that – although it’d be better if you used date formatting functions instead of cutting the date off – or via CAST(... AS DATE) or CONVERT(DATE, ...), which has been mentioned quite often on this very page.

    – Magnus

    Jun 21, 2013 at 15:08

  • 12

    I recommend changing the answer to SELECT DATEADD(dd, DATEDIFF(dd, 0, @your_date), 0) because then dd can be swapped out for any other datepart keyword to truncate your datetime at an arbitrary level.

    – Michael

    Aug 14, 2014 at 16:08

830

+500

SQLServer 2008 now has a ‘date’ data type which contains only a date with no time component. Anyone using SQLServer 2008 and beyond can do the following:

SELECT CONVERT(date, GETDATE())

5

  • 46

    There is also the ‘time’ data type in SQL2008 which answers the other half of the question of separating date and time.

    Aug 25, 2011 at 0:01

  • 10

    FYI, I benchmarked different methods of trimming off time from dates and this was the fastest method. Granted the difference was small, but it was clearly faster over a large # of executions.

    Jul 3, 2014 at 12:48

  • 2

    wt about sqlserver 2005??

    – Dr. MAF

    Nov 19, 2015 at 9:10

  • @Dr.MAF Completing the circle, the pre-2008 answer is here: stackoverflow.com/questions/113045/…

    – Frosty840

    Jul 31, 2017 at 7:24

  • In SQL 2019, what is preferred – this answer or CAST or CONVERT?

    – variable

    Feb 2 at 6:26

211

If using SQL 2008 and above:

select cast(getdate() as date)

13

  • 3

    @FredrickGauss: What type, Date? What version of SQL Server do you use?

    Dec 13, 2012 at 20:01

  • 8

    Beware! declare @date1 datetime = ‘2015-09-30 20:59:59.999’; select cast(@date1 as date) returns ‘2015-10-01’

    – Nick

    Sep 24, 2015 at 19:18

  • 8

    @Nick: this is the issue with DateTime. use DateTime2 instead and it works fine. sqlfiddle.com/#!6/9eecb7/2833

    Sep 25, 2015 at 1:33

  • 10

    @Nick, to complement abatishchev response, your @date1 is indeed 2015-10-01, due to DateTime limitations. Try without any cast to Date, it yields 2015-10-01too! declare @date1 datetime = '2015-09-30 23:59:59.999';select @date1 => 2015-10-01

    Dec 11, 2015 at 17:07

  • 5

    One of these easy to remember SQL tricks. As Mike says, only 2008 onward but, if you find a 2005 and previous DB somewhere, you may have a lot of issues 🙂

    – NicVerAZ

    Dec 29, 2015 at 17:04