If you are looking to get a date datatype without the time, even if the time is 00:00:00 then you are out of luck, you can get a varchar but the structure is a datetime and you will always have some time.
–
Quintin RobinsonSep 22 '08 at 3:39

+1 Looks like this one is 35% faster than the double convert() method commonly used (which I also have used for years). Nice one.
–
DaneSep 22 '08 at 4:04

3

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
–
Jim BirchallSep 24 '08 at 8:25

7

+1 I think it´s really clear and simple, it works and you don´t need to worry about locale.
–
ClaudiaJul 27 '11 at 14:12

23

@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.
–
ErikEAug 17 '12 at 22:03

3

@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.
–
MagnusJun 21 '13 at 15:08

There is also the 'time' data type in SQL2008 which answers the other half of the question of separating date and time.
–
misteraidanAug 25 '11 at 0:01

2

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.
–
GhostJul 3 '14 at 12:48

DATEADD and DATEDIFF are better than CONVERTing to varchar. Both queries have the same execution plan, but execution plans are primarly 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.

Be careful when you're testing your results. Selecting many rows to the client will hide the performance difference becauses 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 rowset 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.

The 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.
–
ErikESep 12 '10 at 23:01

Awesome, thanks! Let me add that I am a performance freak, so I don't know how this compares for performance, but in this case, as often will be the case, I just needed a very quick manual report, in which case milliseconds (even seconds) are totally irrelevant.
–
Nicholas PetersenMay 29 '13 at 19:38

IF you want to use CONVERT and get the same output as in the original question posed, that is, yyyy-mm-dd then use CONVERT(varchar(10),[SourceDate as dateTime],121) same code as the previous couple answers, but the code to convert to yyyy-mm-dd with dashes is 121.

If I can get on my soapbox for a second, this kind of formatting doesn't belong in the data tier, and that's why it wasn't possible without silly high-overhead 'tricks' until SQL Server 2008 when actual datepart data types are introduced. Making such conversions in the data tier is a huge waste of overhead on your DBMS, but more importantly, the second you do something like this, you have basically created in-memory orphaned data that I assume you will then return to a program. You can't put it back in to another 3NF+ column or compare it to anything typed without reverting, so all you've done is introduced points of failure and removed relational reference.

You should ALWAYS go ahead and return your dateTime data type to the calling program and in the PRESENTATION tier, make whatever adjustments are necessary. As soon as you go converting things before returning them to the caller, you are removing all hope of referential integrity from the application. This would prevent an UPDATE or DELETE operation, again, unless you do some sort of manual reversion, which again is exposing your data to human/code/gremlin error when there is no need.

Except, 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…)
–
Andrew LazarusMar 14 '13 at 16:42

@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.
–
Aaron BertrandNov 16 '13 at 5:09

@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.
–
Andrew LazarusNov 16 '13 at 17:04

@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.
–
Aaron BertrandNov 16 '13 at 20:25

@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?
–
Andrew LazarusNov 18 '13 at 23:42

These methods are all great, but which single one do you suggest using?
–
eddiegrovesSep 22 '08 at 3:48

Note that the "correct" version of the top two is select dateadd(dd, datediff(dd, 0, getdate()), 0), because the dds can then be swapped out for any of the datepart keywords to clip the date at any segment you choose. (Also note that dd is just an abbreviation for day.)
–
MichaelAug 14 '14 at 16:02