How to return only the Date from a SQL Server DateTime datatype
Solution 1
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
Solution 2
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())
Solution 3
If using SQL 2008 and above:
select cast(getdate() as date)
Solution 4
DATEADD and DATEDIFF are better than CONVERTing to varchar. Both queries have the same execution plan, but execution plans are primarily about data access strategies and do not always reveal implicit costs involved in the CPU time taken to perform all the pieces. If both queries are run against a table with millions of rows, the CPU time using DateDiff can be close to 1/3rd of the Convert CPU time!
To see execution plans for queries:
set showplan_text on
GO
Both DATEADD and DATEDIFF will execute a CONVERT_IMPLICIT.
Although the CONVERT solution is simpler and easier to read for some, it is slower. There is no need to cast back to DateTime (this is implicitly done by the server). There is also no real need in the DateDiff method for DateAdd afterward as the integer result will also be implicitly converted back to DateTime.
SELECT CONVERT(varchar, MyDate, 101) FROM DatesTable
|--Compute Scalar(DEFINE:([Expr1004]=CONVERT(varchar(30),[TEST].[dbo].[DatesTable].[MyDate],101)))
|--Table Scan(OBJECT:([TEST].[dbo].[DatesTable]))
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, MyDate)) FROM DatesTable
|--Compute Scalar(DEFINE:([Expr1004]=dateadd(day,(0),CONVERT_IMPLICIT(datetime,datediff(day,'1900-01-01 00:00:00.000',CONVERT_IMPLICIT(datetime,[TEST].[dbo].[DatesTable].[MyDate],0)),0))))
|--Table Scan(OBJECT:([TEST].[dbo].[DatesTable]))
Using FLOOR() as @digi suggested has performance closer to DateDiff, but is not recommended as casting the DateTime data type to float and back does not always yield the original value.
Remember guys: Don't believe anyone. Look at the performance statistics, and test it yourself!
Be careful when you're testing your results. Selecting many rows to the client will hide the performance difference because it takes longer to send the rows over the network than it does to perform the calculations. So make sure that the work for all the rows is done by the server but there is no row set sent to the client.
There seems to be confusion for some people about when cache optimization affects queries. Running two queries in the same batch or in separate batches has no effect on caching. So you can either expire the cache manually or simply run the queries back and forth multiple times. Any optimization for query #2 would also affect any subsequent queries, so throw out execution #1 if you like.
Here is full test script and performance results that prove DateDiff is substantially faster than converting to varchar.
Solution 5
Try this:
SELECT CONVERT(VARCHAR(10),GETDATE(),111)
The above statement converts your current format to YYYY/MM/DD
, please refer to this link to choose your preferable format.
Comments
-
Eddie Groves almost 2 years
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?
-
Ben Hoffstein almost 16 yearsOne thing to note is that SQL Server 2008 includes a separate DATE datatype for storing just dates without the time component. More info here: sql-server-performance.com/articles/dev/datetime_2008_p1.aspx
-
ErikE almost 12 yearsDon't miss this post showing performance testing results of the various time-removal methods.
-
Adir D over 10 years@Martin weird, thanks, let's try again.
-
Rohit Vipin Mathews over 10 yearsDon't be mislead by the votes and accepted answer, Take a look at stackoverflow.com/a/126984/1155650
-
hktegner over 10 years@Rohit not everyone has SQL Server 2008 or later.
-
Rohit Vipin Mathews over 10 years@hktegner - it must be misleading for those who have. Also there is one version below 2008 (ie:2005) where as after it we have 4 and counting.
-
hktegner over 10 years@Rohit You are incorrectly assuming that 2008 is the only version people care about. (There are more versions in the wild.) The votes speak for themselves.
-
eaglei22 over 4 yearsFor future readers: mssqltips.com/sqlservertip/1145/… is also useful for getting the correct format when using convert.
-
Admin almost 3 yearsSELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date))
-
-
Eddie Groves almost 16 yearsThis returns '2008/09/22' for me
-
Eddie Groves almost 16 yearsIs this way better or worse performance wise than using the convert methods other have suggested? Or is it negligible?
-
Eddie Groves almost 16 yearsThese methods are all great, but which single one do you suggest using?
-
aku almost 16 yearsMy method works faster. It doesn't require conversions to varchar and allows efficient date calculations
-
Dane almost 16 years+1 Looks like this one is 35% faster than the double convert() method commonly used (which I also have used for years). Nice one.
-
Cade Roux almost 16 yearsIf this is 35% than the CONVERT method, you've got to wonder how much faster a built-in truncate would be - this has to be the most common datetime-related operation I ever do - I'm going to see about switching to this mechanism.
-
aku almost 16 yearsRicardo C, nice investigation! What version of SQL server do you use? On MSSQL2000 method with datediff performs slightly faster for me.
-
aku almost 16 yearsJust to note, I performed test 1000.000 times. For real-world scenarios performance difference will not be noticeable, I guess
-
Ricardo C almost 16 yearsAku, I used SQL Server 2005 Express for this test. I work on 2000 at work, and I will test it with a table with over 24 million rows and see what comes out of it.
-
Jim Birchall almost 16 yearsThe 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
-
Ricardo C almost 16 yearsAku, same results. No difference in performance over ten million rows.
-
ErikE almost 14 yearsThe claims about equal performance are not true. Of course the execution plans will be the same!!! Measuring performance on these MUST be done by comparing CPU usage, not examining execution plans.
-
ErikE almost 14 years+1 You may be interested to see Ricardo C's edited answer (since it is community wiki and factually incorrect, I corrected it). You also got a prop to your question.
-
ErikE almost 14 yearsThis method is not the fastest, and also implicitly teaches people that casting dates to float is accurate, which it is not. Please see this post for more detail.
-
ErikE almost 14 yearsAlso don't miss this post showing performance testing results.
-
misteraidan almost 13 yearsThere is also the 'time' data type in SQL2008 which answers the other half of the question of separating date and time.
-
ErikE almost 12 years@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 wantConvert(varchar(30), @Date, 101)
or something similar. See SQL Server Books Online • Cast and Convert for more info. -
Fredrick Gauss over 11 yearsMsg 243, Level 16, State 1, Line 1 Type date is not a defined system type.
-
abatishchev over 11 years@FredrickGauss: What type, Date? What version of SQL Server do you use?
-
Andrew Lazarus over 11 yearsExcept, say, if you want a query that retrieves all records matching a user-supplied date as the date-part of a certain time field. Good luck doing that only in the presentation layer. (You don't need convert, you can can use date arithmetic, but you get the idea…)
-
N t about 11 yearsWorth noting that this does not extend to aggregating monthly, or yearly data. You have to alter the terms in order for that to work. SELECT DATEADD(mm,DATEDIFF(mm, 0, @YourDate),0) is extensible for mm/yy iirc
-
Praveen about 11 years@aku Is there a way to get only Datepart as "2008-09-22" and not "2008-09-22 00:00:00.000" without converting it into VARCHAR. Now I'm using
CONVERT(VARCHAR(10), @dateTime, 101) AS MyDate
-
Magnus about 11 years@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)
orCONVERT(DATE, ...)
, which has been mentioned quite often on this very page. -
Flea almost 11 yearsSELECT CONVERT(VARCHAR(10),GETDATE(),101) is
mm/dd/yyyy
format. -
aruno almost 11 yearsif you're sorting based on the raw text value (outside of the DB) then the 'japanese' format is better
-
Adir D over 10 years@Andrew why does that matter? You say
WHERE col >= @Date AND col < DATEADD(DAY, 1, @Date);
- there is absolutely no reason to strip time from the column. -
Andrew Lazarus over 10 years@AaronBertrand That only works assuming the input
@Date
has a zero time part. In case that isn't true, you still need to know how to truncate times server-side. I agree with this answer that formatting should be left to the presentation layer, but I didn't agree with an implication that leaving that for the front end means you don't have to know a quick way to truncate. -
Adir D over 10 years@Andrew all you have to do is make the input parameter DATE. My point is still that you should never have to apply any such truncation to the column, even though that is most people's first instinct.
-
Andrew Lazarus over 10 years@AaronBertrand and that assumes you have control over the datatype of the parameter. Fine in a stored procedure, not so possible in other situations. Why not cast to be sure the parameter is the type you want and need?
-
Adir D over 10 years@Andrew again, that's fine. My point - AGAIN - is that you should never have to cast the column. Please read.
-
Bohemian over 10 yearsThis returns me date with zero time, not just date
-
Mahesh ML over 10 yearscan i know which version if sql server you are using?
-
Marek about 10 years@MaheshML it returns both date and time in MS SQL 2012.
-
Martín Coll about 10 yearsWorks like a charm in SQL Azure
-
UnhandledExcepSean almost 10 yearsFYI, 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.
-
Michael almost 10 yearsNote that the "correct" version of the top two is
select dateadd(dd, datediff(dd, 0, getdate()), 0)
, because thedd
s can then be swapped out for any of thedatepart
keywords to clip the date at any segment you choose. (Also note thatdd
is just an abbreviation forday
.) -
Michael almost 10 yearsI recommend changing the answer to
SELECT DATEADD(dd, DATEDIFF(dd, 0, @your_date), 0)
because thendd
can be swapped out for any otherdatepart
keyword to truncate yourdatetime
at an arbitrary level. -
Andriy M over 9 yearsThis suggestion has been covered by other answers (more than once).
-
Nick almost 9 yearsBeware! declare @date1 datetime = '2015-09-30 20:59:59.999'; select cast(@date1 as date) returns '2015-10-01'
-
Nick almost 9 years@abatishchev sorry, that should have been
declare @date1 datetime = '2015-09-30 23:59:59.999';select cast(@date1 as date)
-
abatishchev almost 9 years@Nick: this is the issue with DateTime. use
DateTime2
instead and it works fine. sqlfiddle.com/#!6/9eecb7/2833 -
Dr. MAF over 8 yearswt about sqlserver 2005??
-
Frédéric over 8 years@Nick, to complement abatishchev response, your @date1 is indeed
2015-10-01
, due toDateTime
limitations. Try without any cast toDate
, it yields2015-10-01
too!declare @date1 datetime = '2015-09-30 23:59:59.999';select @date1
=>2015-10-01
-
The1nk over 8 yearsThe question states SQL Server. This seems like MySQL?
-
NicVerAZ over 8 yearsOne 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 :)
-
abatishchev over 8 years@NixVerAZ I believe there are exactly 0 reasons to run SQL Server 2005 in late 2016. This is pure idiocy, isn't it? A good sign of something terribly wrong there.
-
Zack about 8 yearsYour first example still has a time component. The point of the question was how to remove that.
-
galaxis about 7 yearsI like the use of the ANSI std "CAST()" - if portability is a concern (or even achievable) these days :). Also preferred if wanting to preserve precision: msdn.microsoft.com/en-us/library/ms187928.aspx
-
Frosty840 almost 7 years@Dr.MAF Completing the circle, the pre-2008 answer is here: stackoverflow.com/questions/113045/…
-
SvenAelterman almost 7 years@MaheshML There is no such thing as SQL Server 2010.
-
Luc VdV over 6 years@abatischchev - 1 reason that applies to more instances than you'd hold for possible: management refusing to approve the budget for updating antiques as long as someone manages to keep them running. Even SQL Server 2000 running on Windows 2000 is still alive. SQL Server 2005 Express: about 50 instances in my company alone (no, not mine, the one I work for).
-
Luc VdV over 6 yearsThe '2015-09-30 20:59:59.999' issue doesn't seem to apply to Sql Server 2016 anymore.
-
David Faber over 6 yearsOne can go even further with the ANSI standard by using
SELECT CAST(CURRENT_TIMESTAMP AS DATE)
(CURRENT_TIMESTAMP
is equivalent toGETDATE()
). -
David Faber over 6 yearsNot only is it for Oracle, not MS SQL - it's not even correct. To get the date part only from Oracle, one would use
TRUNC(SYSDATE)
-
karthik kasubha over 5 yearsselect cast(createddate as date) from table where createdate is your datetime column
-
J. Chris Compton about 5 yearsI expect "SQL Server 2010" means SQL Server 2008 R2 (it was released in 2010)
-
ChrisM almost 5 yearsI think you have copied in 2 select statements but copied the second select inside the first select.
-
brianary over 4 years
select {fn current_date()} as today
works for me. -
lit over 4 years@brianary - That's nice, but it is not ANSI SQL.
-
brianary over 4 yearsThat's fair enough, and your answer is nicely portable, but I figured as long as we're working around T-SQL, this also works (and shows that implementing ANSI CURRENT_DATE would be trivial for MS).
-
noobprogrammer over 4 yearsDATE is not a function dude.
-
ourmandave over 3 yearsTo skip the culture, the custom formats lets you set your own, e.g.
FORMAT (@d, 'yyyyy-MM-dd')
to get 2011-10-11. -
Nosajimiki almost 3 yearsTo clarify... DATE() is a function in mySQL, but this question is about SQL Server. In mySQL you can use the DATE() function to extract the date from a date time element such that DATE("2021-06-15 09:34:21") returns "2021-06-15", but GETDATE() is not a mySQL function, so he is mix-matching SQL languages. The equivalent of this in mySQL would be DATE(NOW())... but mySQL makes this completely unnecessary since you can just call CURDATE() instead.
-
Nosajimiki almost 3 yearsThe last 3 are full dateTime stamps being truncated. 121 is not separated by a 'T' while the other two are. 127 is more precise when working with DATETIME2 formats, but with regular datetimes, they round to the same values; so, in a different context, these return 3 different datetime values. As for the second one, A = B can be used in slightly different ways than A AS B using other functions than this, but using these functions, they work the same. As for Convert vs Cast, Cast is a native ANSI function while Convert is SQL specific. Convert has more options but not always as performant.
-
Auspex almost 3 yearsI prefer this over the
CONVERT()
syntax as it's more standards compliant (though, of course,CURRENT_TIMESTAMP
) -
Aubrey Love over 2 yearsJust my 2 cents worth, but the first SELECT statement: “select convert(getdate() as date)” does not work. The “date” and “getdate()” should have been inverted like in the code below. “SELECT CONVERT(DATE, GETDATE() )” You could also use the “CAST()” function as well. Either one will return the same results. “SELECT CAST(GETDATE() AS DATE)”
-
variable over 2 yearsIn SQL 2019, what is preferred - this answer or CAST or CONVERT?
-
Jeff Moden about 2 yearsUsing FORMAT in any case in SQL Server is at least 20 times slower than even some of the craziest conversions that you can think of. I'd stay away from it for everything.