Best approach to remove time part of datetime in SQL Server

Question

Which method provides the best performance when removing the time portion from a datetime field in SQL Server   a  select DATEADD dd  DATEDIFF dd  0  getdate     0    or  b  select cast convert char 11   getdate    113  as datetime    The second method does send a few more bytes either way but that might not be as important as the speed of the conversion   Both also appear to be very fast  but there might be a difference in speed when dealing with hundreds-of-thousands or more rows   Also  is it possible that there are even better methods to get rid of the time portion of a datetime in SQL

User · Answer

I really like    date    CONVERT VARCHAR 10   GETDATE    120    The 120 format code will coerce the date into the ISO 8601 standard    YYYY-MM-DD  or  2017-01-09    Super easy to use in dplyr  R  and pandas  Python

User · Answer

Strip time on inserts updates in the first place  As for on-the-fly conversion  nothing can beat a user-defined function maintanability-wise   select date only dd    The implementation of date only can be anything you like - now it s abstracted away and calling code is much much cleaner

User · Answer

Here s yet another answer  from another duplicate question   SELECT CAST CAST getutcdate   - 0 50000004 AS int  AS datetime     This magic number method performs slightly faster than the DATEADD method   It looks like  10    The CPU Time on several rounds of a million records   DATEADD   MAGIC FLOAT 500       453 453       360 375       375 406       360   But note that these numbers are possibly irrelevant because they are already VERY fast   Unless I had record sets of 100 000 or more  I couldn t even get the CPU Time to read above zero   Considering the fact that DateAdd is meant for this purpose and is more robust  I d say use DateAdd

User · Answer

For me the code below is always a winner   SELECT CONVERT DATETIME  FLOOR CONVERT FLOAT GETDATE

User · Answer

SELECT CAST FLOOR CAST getdate   AS FLOAT   AS DATETIME       is not a good solution  per the comments below    I would delete this answer  but I ll leave it here as a counter-example since I think the commenters  explanation of why it s not a good idea is still useful

User · Answer

select CONVERT char 10   GetDate   126

User · Answer

In SQL Server 2008  there is a DATE datetype  also a TIME datatype    CAST GetDate   as DATE    or  declare  Dt as DATE   GetDate

User · Answer

I think that if you stick strictly with TSQL that this is the fastest way to truncate the time    select convert datetime convert int convert float  Modified       I found this truncation method to be about 5  faster than the DateAdd method  And this can be easily modified to round to the nearest day like this   select convert datetime ROUND convert float  Modified   0

User · Answer

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   varchar is subject to language dateformat issues Example  Why is my CASE expression non-deterministic  float relies on internal storage it extends to work out first day of month  tomorrow  etc by changing  quot 0 quot  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 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

User · Answer

In SQL Server 2008  you can use   CONVERT DATE  getdate    101

User · Answer

Just in case anyone is looking in here for a Sybase version since several of the versions above didn t work   CAST CONVERT DATE GETDATE   103  AS DATETIME     Tested in I SQL v11 running on Adaptive Server 15 7

User · Answer

I would use   CAST   CAST YEAR DATEFIELD  as varchar 4         CAST MM DATEFIELD  as varchar 2         CAST DD DATEFIELD  as varchar 2   as datetime      Thus effectively creating a new field from the date field you already have

User · Answer

BEWARE   Method a  and b  does NOT always have the same output   select DATEADD dd  DATEDIFF dd  0   2013-12-31 23 59 59 999    0    Output  2014-01-01 00 00 00 000  select cast convert char 11    2013-12-31 23 59 59 999   113  as datetime    Output  2013-12-31 00 00 00 000   Tested on MS SQL Server 2005 and 2008 R2   EDIT  According to Adam s comment  this cannot happen if you read the date value from the table  but it can happen if you provide your date value as a literal  example  as a parameter of a stored procedure called via ADO NET

User · Answer

Here I made a function to remove some parts of a datetime for SQL Server  Usage    First param is the datetime to be stripped off  Second param is a char     s  rounds to seconds  removes milliseconds m  rounds to minutes  removes seconds and milliseconds h  rounds to hours  removes minutes  seconds and milliseconds  d  rounds to days  removes hours  minutes  seconds and milliseconds   Returns the new datetime   create function dbo uf RoundDateTime  dt as datetime   part as char      returns datetime as begin     if CHARINDEX   part   smhd  0    0 return  dt      return cast          Case  part             when  s  then convert varchar 19    dt  126              when  m  then convert varchar 17    dt  126     00              when  h  then convert varchar 14    dt  126     00 00              when  d  then convert varchar 14    dt  112         end as datetime   end

User · Answer

I think you mean    cast floor cast getdate  as float  as datetime   real is only 32-bits  and could lose some information  This is fastest cast cast getdate   x-0 5 as int as datetime      though only about 10  faster about 0 49 microseconds CPU vs  0 58   This was recommended  and takes the same time in my test just now  DATEADD dd  DATEDIFF dd  0  getdate     0   In SQL 2008  the SQL CLR function is about 5 times faster than using a SQL function would be  at 1 35 microseconds versus 6 5 microsections  indicating much lower function-call overhead for a SQL CLR function versus a simple SQL UDF   In SQL 2005  the SQL CLR function is 16 times faster  per my testing  versus this slow function   create function dateonly     dt datetime   returns datetime as begin return cast floor cast  dt as float  as int  end

User · Answer

See this question  How can I truncate a datetime in SQL Server   Whatever you do  don t use the string method   That s about the worst way you could do it

User · Answer

Of-course this is an old thread but to make it complete   From SQL 2008 you can use DATE datatype so you can simply do   SELECT CONVERT DATE GETDATE

User · Answer

If possible  for special things like this  I like to use CLR functions   In this case    Microsoft SqlServer Server SqlFunction      public static SqlDateTime DateOnly SqlDateTime input                if   input IsNull                        SqlDateTime dt   new SqlDateTime input Value Year  input Value Month  input Value Day  0  0  0                return dt                    else             return SqlDateTime Null

User · Answer

CAST round cast getdate  as real  0 1  AS datetime    This method does not use string function  Date is basically a real datatype with digits before decimal are fraction of a day    this I guess will be faster than a lot

User · Answer

How about select cast cast my datetime field as date  as datetime    This results in the same date  with the time set to 00 00  but avoids any conversion to text and also avoids any explicit numeric rounding

User · Answer

SELECT CAST CAST GETDATE   AS DATE  AS DATETIME

User · Answer

I  personally  almost always use User Defined functions for this if dealing with SQL Server 2005  or lower version   however  it should be noted that there are specific drawbacks to using UDF s  especially if applying them to WHERE clauses  see below and the comments on this answer for further details    If using SQL Server 2008  or higher  - see below   In fact  for most databases that I create  I add these UDF s in right near the start since I know there s a 99  chance I m going to need them sooner or later   I create one for  date only   amp   time only   although the  date only  one is by far the most used of the two    Here s some links to a variety of date-related UDF s   Essential SQL Server Date  Time and DateTime Functions Get Date Only Function    That last link shows no less than 3 different ways to getting the date only part of a datetime field and mentions some pros and cons of each approach   If using a UDF  it should be noted that you should try to avoid using the UDF as part of a WHERE clause in a query as this will greatly hinder performance of the query   The main reason for this is that using a UDF in a WHERE clause renders that clause as non-sargable  which means that SQL Server can no longer use an index with that clause in order to improve the speed of query execution   With reference to my own usage of UDF s  I ll frequently use the  raw  date column within the WHERE clause  but apply the UDF to the SELECTed column   In this way  the UDF is only applied to the filtered result-set and not every row of the table as part of the filter   Of course  the absolute best approach for this is to use SQL Server 2008  or higher  and separate out your dates and times  as the SQL Server database engine is then natively providing the individual date and time components  and can efficiently query these independently without the need for a UDF or other mechanism to extract either the date or time part from a composite datetime type

User · Answer

Already answered but ill throw this out there too    this suposedly also preforms well but it works by throwing away the decimal  which stores time  from the float and returning only whole part  which is date    CAST  FLOOR  CAST  GETDATE   AS FLOAT     AS DATETIME     second time I found this solution    i grabbed this code off

[sql] Best approach to remove time part of datetime in SQL Server

Examples related to sql

Examples related to sql-server

Examples related to tsql

Examples related to datetime

Examples related to date