Best approach to remove time part of datetime in SQL Server

by

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?

I saw a similar test elsewhere with similar results too.

I prefer the DATEADD/DATEDIFF because:

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

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

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)

More Related Contents:

  1. How to return only the Date from a SQL Server DateTime datatype
  2. Convert text to system date format [duplicate]
  3. DateTime2 vs DateTime in SQL Server
  4. SQL query to select dates between two dates
  5. How can I select the first day of a month in SQL?
  6. How can I convert bigint (UNIX timestamp) to datetime in SQL Server?
  7. How to determine the number of days in a month in SQL Server?
  8. SQL “between” not inclusive
  9. How to compare datetime with only date in SQL Server
  10. Getting Dates between a range of dates
  11. Date serial in SQL?
  12. How to get a particular preceding value of a column in SQL [closed]
  13. What is the meaning of the prefix N in T-SQL statements and when should I use it?
  14. SQL Server : Columns to Rows
  15. get a comma delimited string from rows [duplicate]
  16. Find all tables containing column with specified name – MS SQL Server
  17. How important is the order of columns in indexes?
  18. How to split a single column values to multiple column values?
  19. Why no windowed functions in where clauses?
  20. T-SQL: Round to nearest 15 minute interval
  21. T-SQL Cast versus Convert
  22. What is a stored procedure?
  23. T-SQL datetime rounded to nearest minute and nearest hours with using functions
  24. convert Excel Date Serial Number to Regular Date
  25. Partition Function COUNT() OVER possible using DISTINCT
  26. SQL SERVER: Get total days between two dates
  27. Alter user defined type in SQL Server
  28. Store multiple bit values in a single table column
  29. SQL Server equivalent to MySQL enum data type?
  30. How to correctly insert newline in nvarchar [duplicate]

Leave a Comment