Rounding SQL DateTime to midnight

Question

I am having a small problem with my SQL query  I m using the GETDATE function  however  let s say I execute the script at 5PM  it will pull up records between 12 12 2011 5PM to 12 18 2011 5PM  How can I make it pull up records for the whole entire 12 12 2011 - 12 18 2011 basically ignore time   My script   WHERE Orders OrderStatus    Shipped    AND Orders ShipDate  gt   GETDATE  -6

User · Answer

Here is the simplest thing I ve found  -- Midnight floor of current date  SELECT Convert DateTime  DATEDIFF DAY  0  GETDATE       The DATEDIFF returns the integer number of days before or since 1900-1-1  and the Convert Datetime obligingly brings it back to that date at midnight   Since DateDiff returns an integer you can use add or subtract days to get the right offset   SELECT Convert DateTime  DATEDIFF DAY  0  GETDATE       dayOffset    This isn t rounding this is truncating   But I think that is what is being asked   To round add one and truncate   and that s not rounding either  that the ceiling  but again most likely what you want    To really round add  5  does that work   and truncate   It turns out you can add  5 to GetDate   and it works as expected   -- Round Current time to midnight today or midnight tomorrow  SELECT Convert DateTime  DATEDIFF DAY  0  GETDATE      5     I did all my trials on SQL Server 2008  but I think these functions apply to 2005 as well

User · Answer

Try using this   WHERE Orders OrderStatus    Shipped    AND Orders ShipDate  gt   CONVERT DATE  GETDATE

User · Answer

You can convert the datetime to a date then back to a datetime  This will reset the timestamp    select getdate   --2020-05-05 13 53 35 863  select cast cast GETDATE   as date  as datetime  --2020-05-05 00 00 00 000

User · Answer

I usually do  SELECT   FROM MyTable WHERE CONVERT VARCHAR  MyTable dateField  101    CONVERT VARCHAR  GETDATE    101    if you are using SQL SERVER 2008  you can do  SELECT   FROM MyTable WHERE CAST MyTable dateField AS DATE    CAST GETDATE   AS DATE    Hope this helps

User · Answer

In SQL Server 2008 and newer you can cast the DateTime to a Date  which removes the time element   WHERE Orders OrderStatus    Shipped    AND Orders ShipDate  gt    cast GETDATE  -6 as date       In SQL Server 2005 and below you can use   WHERE Orders OrderStatus    Shipped    AND Orders ShipDate  gt   DateAdd Day  Datediff Day 0  GetDate   -6   0

User · Answer

-- -- SQL DATEDIFF getting midnight time parts  -- SELECT GETDATE   AS Now      Convert DateTime  DATEDIFF DAY  0  GETDATE     AS MidnightToday     Convert DateTime  DATEDIFF DAY  -1  GETDATE     AS MidnightNextDay     Convert DateTime  DATEDIFF DAY  1  GETDATE     AS MidnightYesterDay go Now                   MidnightToday          MidnightNextDay        MidnightYesterDay       --------------------  ---------------------  ---------------------  ---------------------   8 27 2014 4 30 22 PM  8 27 2014 12 00 00 AM  8 28 2014 12 00 00 AM  8 26 2014 12 00 00 AM

User · Answer

You could round down the time   Using ROUND below will round it down to midnight   WHERE Orders OrderStatus    Shipped    AND Orders ShipDate  gt   CONVERT datetime   ROUND convert float  getdate  -6 5  0

User · Answer

SELECT getdate     Result  2012-12-14 16 03 33 360  SELECT convert datetime convert bigint  getdate       Result 2012-12-15 00 00 00 000

User · Answer

As  BassamMehanni mentioned  you can cast as DATE in SQL Server 2008 onwards     SELECT     FROM   yourTable WHERE       dateField  gt   CAST GetDate   - 6 AS DATE    AND dateField  lt   CAST GetDate     1 AS DATE    The second condition can actually be just GetDate    but I m showing this format as an example of Less Than DateX to avoid having to cast the dateField to a DATE as well  thus massively improving performance     If you re on 2005 or under  you can use this     SELECT     FROM   yourTable WHERE       dateField  gt   DATEADD DAY  DATEDIFF DAY  0  GetDate    - 6  0    AND dateField  lt   DATEADD DAY  DATEDIFF DAY  0  GetDate      1  0

User · Answer

This might look cheap but it s working for me     SELECT CONVERT DATETIME LEFT CONVERT VARCHAR  dateFieldOrVariable 101  10        00 00 00 000

[sql] Rounding SQL DateTime to midnight

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2005

Examples related to getdate