How can I truncate a datetime in SQL Server

Question

What s the best way to truncate a datetime value  as to remove hours minutes and seconds  in SQL Server 2008   For example   declare  SomeDate datetime    2009-05-28 16 30 22  select trunc date  SomeDate   ----------------------- 2009-05-28 00 00 00 000

User · Answer

TRUNC aDate   DD   will truncate the min  sec and hrs  SRC  http   www techonthenet com oracle functions trunc date php

User · Answer

For those of you who came here looking for a way to truncate a DATETIME field to something less than a whole day  for example every minute  you can use this   SELECT CAST FLOOR CAST GETDATE   AS FLOAT      FLOOR  CAST GETDATE   AS FLOAT  - FLOOR CAST GETDATE   AS FLOAT      1440 0     3 0 86400000 0     1440 0 AS DATETIME    so if today was 2010-11-26 14 54 43 123 then this would return 2010-11-26 14 54 00 000   To change the interval it trucates to  replace 1440 0 with the number of intervals in a day  for example   24hrs              24 0   for every hour  24hrs   0 5hrs     48 0   for every half hour  24hrs    1 60    1440 0   for every minute     Always put a  0 on the end to implicitly cast to a float      For those of you wondering what the  3 0 86400000  is for in my calculation  SQL Server 2005 doesn t seem to cast from FLOAT to DATETIME accurately  so this adds 3 milliseconds before flooring it

User · Answer

Just for the sake of a more complete answer  here s a working way for truncating to any of the date parts down and including minutes  replace GETDATE   with the date to truncate    This is different from the accepted answer in that you can use not only dd  days   but any of the date parts  see here    dateadd minute  datediff minute  0  GETDATE     0    Note that in the expression above  the 0 is a constant date on the beginning of a year  1900-01-01   If you need to truncate to smaller parts  such as to seconds or milliseconds  you need to take a constant date which is closer to the date to be truncated to avoid an overflow

User · Answer

Oracle    TRUNC SYSDATE   MONTH     SQL Server    DATEADD DAY  - DATEPART DAY  DateField    1  DateField    Could be similarly used for truncating minutes or hours from a date

User · Answer

you could just do this  SQL 2008       declare  SomeDate date   getdate     select  SomeDate     2009-05-28

User · Answer

select cast floor cast getdate   as float   as datetime  Reference this  http   microsoftmiles blogspot com 2006 11 remove-time-from-datetime-in-sql-server html

User · Answer

The snippet I found on the web when I had to do this was    dateadd dd 0  datediff dd 0  YOURDATE    e g   dateadd dd 0  datediff dd 0  getDate

User · Answer

CONVERT DATE   lt yourdatetime gt   or CONVERT DATE  GetDate    or CONVERT DATE  CURRENT TIMESTAMP

User · Answer

For SQL Server 2008 only  CAST  SomeDateTime AS Date     Then cast it back to datetime if you want  CAST CAST  SomeDateTime AS Date  As datetime

User · Answer

This query should give you result equivalent to trunc sysdate  in Oracle   SELECT     FROM    your table WHERE   CONVERT varchar 12   your column name  101          CONVERT varchar 12   GETDATE    101    Hope this helps

User · Answer

This continues to frequently gather additional votes  even several years later  and so I need to update it for modern versions of Sql Server  For Sql Server 2008 and later  it s simple   cast getDate   As Date    Note that the last three paragraphs near the bottom still apply  and you often need to take a step back and find a way to avoid the cast in the first place   But there are other ways to accomplish this  too  Here are the most common   The correct way  new since Sql Server 2008    cast getdate   As Date    The correct way  old    dateadd dd  datediff dd 0  getDate     0    This is older now  but it s still worth knowing because it can also easily adapt for other time points  like the first moment of the month  minute  hour  or year   This correct way uses documented functions that are part of the ansi standard and are guaranteed to work  but it can be somewhat slower  It works by finding how many days there are from day 0 to the current day  and adding that many days back to day 0  It will work no matter how your datetime is stored and no matter what your locale is   The fast way   cast floor cast getdate   as float   as datetime    This works because datetime columns are stored as 8-byte binary values  Cast them to float  floor them to remove the fraction  and the time portion of the values are gone when you cast them back to datetime  It s all just bit shifting with no complicated logic and it s very fast    Be aware this relies on an implementation detail Microsoft is free to change at any time  even in an automatic service update  It s also not very portable  In practice  it s very unlikely this implementation will change any time soon  but it s still important to be aware of the danger if you choose to use it  And now that we have the option to cast as a date  it s rarely necessary   The wrong way   cast convert char 11   getdate    113  as datetime    The wrong way works by converting to a string  truncating the string  and converting back to a datetime  It s wrong  for two reasons  1 it might not work across all locales and 2  it s about the slowest possible way to do this     and not just a little  it s like an order of magnitude or two slower than the other options      Update This has been getting some votes lately  and so I want to add to it that since I posted this I ve seen some pretty solid evidence that Sql Server will optimize away the performance difference between  correct  way and the  fast  way  meaning you should now favor the former   In either case  you want to write your queries to avoid the need to do this in the first place   It s very rare that you should do this work on the database     In most places  the database is already your bottleneck   It s generally the server that s the most expensive to add hardware to for performance improvements and the hardest one to get those additions right  you have to balance disks with memory  for example    It s also the hardest to scale outward  both technically and from a business standpoint  it s much easier technically to add a web or application server than a database server and even if that were false you don t pay  20 000  per server license for IIS or apache     The point I m trying to make is that whenever possible you should do this work at the application level   The only time you should ever find yourself truncating a datetime on Sql Server is when you need to group by the day  and even then you should probably have an extra column set up as a computed column  maintained at insert update time  or maintained in application logic  Get this index-breaking  cpu-heavy work off your database

User · Answer

You can also extract date using Substring from the datetime variable and casting back to datetime will ignore time part   declare  SomeDate datetime    2009-05-28 16 30 22  SELECT cast substring convert varchar 12   SomeDate 111  0 12  as Datetime     Also  you can access parts of datetime variable and merge them to a construct truncated date  something like this   SELECT cast DATENAME year   Somedate     -            Convert varchar 2  DATEPART month   Somedate      -           DATENAME day   Somedate          as datetime

User · Answer

In SQl 2005 your trunc date function could be written like this    1   CREATE FUNCTION trunc date  date DATETIME  RETURNS DATETIME AS BEGIN     CAST FLOOR  CAST   date AS FLOAT    AS DATETIME  END   The first method is much much cleaner  It uses only 3 method calls including the final CAST   and performs no string concatenation  which is an automatic plus  Furthermore  there are no huge type casts here  If you can imagine that Date Time stamps can be represented  then converting from dates to numbers and back to dates is a fairly easy process    2   CREATE FUNCTION trunc date  date DATETIME  RETURNS DATETIME AS BEGIN       SELECT CONVERT varchar   date 112  END   If you are concerned about microsoft s implementation of datetimes  2  or  3  might be ok    3   CREATE FUNCTION trunc date  date DATETIME  RETURNS DATETIME AS BEGIN SELECT CAST  STR  YEAR   date            STR  MONTH   date            STR  DAY  date       AS DATETIME END   Third  the more verbose method  This requires breaking the date into its year  month  and day parts  putting them together in  yyyy mm dd  format  then casting that back to a date  This method involves 7 method calls including the final CAST    not to mention string concatenation

[sql-server] How can I truncate a datetime in SQL Server?

Examples related to sql-server

Examples related to sql-server-2008

Examples related to datetime

Examples related to truncate