CODEWITHSUNDEEP
sqlsql-serversql-server-2005getdate
henryaaron
henryaaron

Reputation: 6192

Rounding SQL DateTime to midnight

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)

Upvotes: 101

Views: 211083

Answers (11)

shayshank
shayshank

Reputation: 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)).

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

Upvotes: -1

Ben
Ben

Reputation: 2023

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

Upvotes: 7

ChrisM
ChrisM

Reputation: 505

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

Upvotes: 0

Jean-Louis Gervais
Jean-Louis Gervais

Reputation: 21

This might look cheap but it's working for me

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

Upvotes: 1

DaveShaw
DaveShaw

Reputation: 52788

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)

Upvotes: 134

edvox1138
edvox1138

Reputation: 401

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

Upvotes: 15

UttamG
UttamG

Reputation: 31

Try using this.

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

Upvotes: 3

Jeremy Atkinson
Jeremy Atkinson

Reputation: 134

SELECT getdate()

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

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

Result 2012-12-15 00:00:00.000

Upvotes: 5

Darrel Lee
Darrel Lee

Reputation: 2470

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.

Upvotes: 67

MatBailie
MatBailie

Reputation: 86706

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)

Upvotes: 3

Bassam Mehanni
Bassam Mehanni

Reputation: 14944

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

Upvotes: 0

Related Questions