Best approach to remove time part of datetime in SQL Server
Best approach to remove time part of datetime in SQL Server
Best approach to remove time part of datetime in SQL Server
Re: Best approach to remove time part of datetime in SQL Server
Strictly, method `a` is the least resource intensive:
```
a) select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)
```
Proven less CPU intensive for the same total duration a million rows by someone with way too much time on their hands: [Most efficient way in SQL Server to get a date from date+time?](https://stackoverflow.com/questions/133081/most-efficient-way-in-sql-server-to-get-date-from-datetime)
I saw a similar test elsewhere with similar results too.
I prefer the DATEADD/DATEDIFF because:
- varchar is subject to language/dateformat issues
Example: [Why is my CASE expression non-deterministic?](https://stackoverflow.com/q/3596663/27535)
- float relies on internal storage
- it extends to work out first day of month, tomorrow, etc by changing "0" base
**Edit, Oct 2011**
For SQL Server 2008+, you can CAST to `date` i.e. `CAST(getdate() AS date)`. Or just use `date` datatype so no `time` to remove.
**Edit, Jan 2012**
A worked example of how flexible this is: [Need to calculate by rounded time or date figure in sql server](https://stackoverflow.com/questions/8722022/need-to-calculate-by-rounded-time-or-date-figure-in-sql-server/8723311#8723311)
**Edit, May 2012**
Do not use this in WHERE clauses and the like without thinking: adding a function or CAST to a column invalidates index usage. See number 2 here [Common SQL Programming Mistakes](http://www.simple-talk.com/sql/t-sql-programming/ten-common-sql-programming-mistakes/)
Now, this does have an example of later SQL Server optimiser versions managing CAST to date correctly, but *generally* it will be a bad idea ...
**Edit, Sep 2018, for datetime2**
```
DECLARE @datetime2value datetime2 = '02180912 11:45' --this is deliberately within datetime2, year 0218
DECLARE @datetime2epoch datetime2 = '19000101'
select DATEADD(dd, DATEDIFF(dd, @datetime2epoch, @datetime2value), @datetime2epoch)
```
```
a) select DATEADD(dd, DATEDIFF(dd, 0, getdate()), 0)
```
Proven less CPU intensive for the same total duration a million rows by someone with way too much time on their hands: [Most efficient way in SQL Server to get a date from date+time?](https://stackoverflow.com/questions/133081/most-efficient-way-in-sql-server-to-get-date-from-datetime)
I saw a similar test elsewhere with similar results too.
I prefer the DATEADD/DATEDIFF because:
- varchar is subject to language/dateformat issues
Example: [Why is my CASE expression non-deterministic?](https://stackoverflow.com/q/3596663/27535)
- float relies on internal storage
- it extends to work out first day of month, tomorrow, etc by changing "0" base
**Edit, Oct 2011**
For SQL Server 2008+, you can CAST to `date` i.e. `CAST(getdate() AS date)`. Or just use `date` datatype so no `time` to remove.
**Edit, Jan 2012**
A worked example of how flexible this is: [Need to calculate by rounded time or date figure in sql server](https://stackoverflow.com/questions/8722022/need-to-calculate-by-rounded-time-or-date-figure-in-sql-server/8723311#8723311)
**Edit, May 2012**
Do not use this in WHERE clauses and the like without thinking: adding a function or CAST to a column invalidates index usage. See number 2 here [Common SQL Programming Mistakes](http://www.simple-talk.com/sql/t-sql-programming/ten-common-sql-programming-mistakes/)
Now, this does have an example of later SQL Server optimiser versions managing CAST to date correctly, but *generally* it will be a bad idea ...
**Edit, Sep 2018, for datetime2**
```
DECLARE @datetime2value datetime2 = '02180912 11:45' --this is deliberately within datetime2, year 0218
DECLARE @datetime2epoch datetime2 = '19000101'
select DATEADD(dd, DATEDIFF(dd, @datetime2epoch, @datetime2value), @datetime2epoch)
```