henryaaron henryaaron - 1 month ago 11
SQL Question

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)

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