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)  

Hepner

Answer 1

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)

Marxmarxian

I got this Type date is not a defined system type. – Hepner

I guess you're not using SQL 2008 then :) – Marxmarxian

@user1090389 that's why I put the string conversion option ;D – Succoth

@Marxmarxian - Missing a DATEADD in there – Zealotry

Sorry @Dems Dave's looks less complicated – Hepner

@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 ;) – Zealotry

@Marxmarxian - Need to change the > to >= for date values that somehow happen at exactly midnight. – Zealotry

@Dems You're right. I just C&P'd from the example "user" posted and C&P'd from my "Scripts" folder (Popfly). – Marxmarxian

Answer 2

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.

Cranium

This works in 2k5 just tested it. where [ScanDate] >= convert(datetime, datediff(day, 0, getdate())) and [ScanDate] < convert(datetime, datediff(day, -1, getdate())) – Donaugh

Answer 3

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

Fructuous

Answer 4

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

Joiner

Answer 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

Versicle

Thanks for the feedback. What I was trying to highlight that the convert to bigint and back does the rounding for you. – Versicle

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. – Delaryd

Using this method can cause performance impacts later with larger datasets. – Cuckooflower

Answer 6

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)

Zealotry

Answer 7

Try using this.

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

Singleton

Answer 8

This might look cheap but it's working for me

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

Delative

Answer 9

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

Succoth

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. – Zealotry

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. – Succoth

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. – Servais

Answer 10

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

Delaryd

Answer 11

Hi Try to use below query

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

I had created dummy data and testested above query works fine.

Simulated

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. – Malaise