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 > (GETDATE()-6)

DaveShaw · Accepted Answer · 2016-11-07T20:08:46.933

132

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 >= (cast(GETDATE()-6 as date))

In SQL Server 2005 and below you can use:

WHERE Orders.OrderStatus = 'Shipped'  
AND Orders.ShipDate >= DateAdd(Day, Datediff(Day,0, GetDate() -6), 0)

edited Nov 07 '16 at 20:08

answered Dec 18 '11 at 22:29

DaveShaw

52,123
16
112
141

1

I got this Type date is not a defined system type. – henryaaron Dec 18 '11 at 22:31
2

I guess you're not using SQL 2008 then :) – DaveShaw Dec 18 '11 at 22:31
@user1090389 that's why I put the string conversion option ;D – Bassam Mehanni Dec 18 '11 at 22:33
@DaveShaw - Missing a DATEADD in there – MatBailie Dec 18 '11 at 22:38
Sorry @Dems Dave's looks less complicated – henryaaron Dec 18 '11 at 22:44
@user1090389 - ***LOL*** the only difference is that I have an extra clause in the WHERE condition as a *demonstration* of how to set both Lower *and* Upper boundaries. Just delete the AND line and it's nearly identical ;) – MatBailie Dec 18 '11 at 22:46
@DaveShaw - Need to change the `>` to `>=` for date values that somehow happen at exactly midnight. – MatBailie Dec 18 '11 at 22:47
@Dems You're right. I just C&P'd from the example "user" posted and C&P'd from my "Scripts" folder (Popfly). – DaveShaw Dec 18 '11 at 22:58

score 65 · Answer 2 · edited Jun 20 '12 at 23:10

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.

This works in 2k5 just tested it. `where [ScanDate] >= convert(datetime, datediff(day, 0, getdate())) and [ScanDate] < convert(datetime, datediff(day, -1, getdate()))` — nulltron, Mar 21 '19 at 20:19

score 13 · Answer 3 · answered Aug 27 '14 at 23:33

--
-- 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

score 7 · Answer 4 · answered May 05 '20 at 17:54

7

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

answered May 05 '20 at 17:54

Ben

1,853
19
20

score 5 · Answer 5 · edited Dec 18 '12 at 02:13

5

SELECT getdate()

Result: 2012-12-14 16:03:33.360

SELECT convert(datetime,convert(bigint, getdate()))

Result 2012-12-15 00:00:00.000

edited Dec 18 '12 at 02:13

Sean Cline

6,979
1
37
50

answered Dec 18 '12 at 01:51

Jeremy Atkinson

134
1
2

1

Thanks for the feedback. What I was trying to highlight that the convert to bigint and back does the rounding for you. – Jeremy Atkinson Dec 18 '12 at 21:50
2

This code rounds it up to the midnight at the end of the day if the time is after midday which causes this to be wrong for half the day. – ChrisM Jul 30 '19 at 11:10
Using this method can cause performance impacts later with larger datasets. – Hazel へいぜる Aug 02 '19 at 15:27

score 3 · Answer 6 · edited May 07 '14 at 09:39

3

Try using this.

WHERE Orders.OrderStatus = 'Shipped'  
AND Orders.ShipDate >= CONVERT(DATE, GETDATE())

edited May 07 '14 at 09:39

09stephenb

9,358
15
53
91

answered May 07 '14 at 09:19

UttamG

31
2

score 3 · Answer 7 · answered Dec 18 '11 at 22:35

As @BassamMehanni mentioned, you can cast as DATE in SQL Server 2008 onwards...

SELECT
  *
FROM
  yourTable
WHERE
      dateField >= CAST(GetDate() - 6 AS DATE)
  AND dateField <  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 >= DATEADD(DAY, DATEDIFF(DAY, 0, GetDate()) - 6, 0)
  AND dateField <  DATEADD(DAY, DATEDIFF(DAY, 0, GetDate()) + 1, 0)

score 1 · Answer 8 · answered Jan 18 '17 at 16:41

1

This might look cheap but it's working for me

SELECT CONVERT(DATETIME,LEFT(CONVERT(VARCHAR,@dateFieldOrVariable,101),10)+' 00:00:00.000')

answered Jan 18 '17 at 16:41

Jean-Louis Gervais

21
2

ChrisM · Answer 9 · 2019-07-30T11:07:43.397

0

You could round down the time.

Using ROUND below will round it down to midnight.

WHERE Orders.OrderStatus = 'Shipped'  
AND Orders.ShipDate >  CONVERT(datetime, (ROUND(convert(float, getdate()-6.5),0)))

edited Jul 30 '19 at 11:07

answered Jul 30 '19 at 11:00

ChrisM

505
6
18

score 0 · Answer 10 · answered Dec 18 '11 at 22:28

0

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

answered Dec 18 '11 at 22:28

Bassam Mehanni

14,796
2
33
41

3

If you use the first example you *destory* the optimiser's ability to use indexes, etc. Using String functions to do Date arithmetic and comparisons is a very bad idea. It shouldn't ever (imo) even be mentioned, it's that poor an option. – MatBailie Dec 18 '11 at 22:30
You don't have to if you are using SQL Server 2008, otherwise, I am not sure how else you could do it in SQL Server 2000/2005, an example would be appreciated, thanks. – Bassam Mehanni Dec 18 '11 at 22:32
To be fair. Both options destroy the optmizer's ability to use indexes. You should perform and functionality on the filter predicate column. This includes casts. – pim May 31 '17 at 17:12

score -1 · Answer 11 · edited Nov 30 '22 at 13:40

-1

Hi Try to use below query

select * from table where column_name <=(cast(GETDATE() as date)) and column_name >=(cast(GETDATE()-1 as date))

edited Nov 30 '22 at 13:40

esqew

42,425
27
92
132

answered Nov 30 '22 at 13:34

shayshank

1
1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Dec 03 '22 at 00:52

Rounding SQL DateTime to midnight

11 Answers11