Rounding SQL DateTime to midnight

sql sql-server sql-server-2005 getdate
henryaaron picture henryaaron · Dec 18, 2011 · Viewed 150.5k times · Source

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

DaveShaw picture DaveShaw · Dec 18, 2011

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)

Related questions

Add a column with a default value to an existing table in SQL Server

How can I add a column with a default value to an existing table in SQL Server 2000 / SQL Server 2005?

Read More
Insert results of a stored procedure into a temporary table

How do I do a SELECT * INTO [temp table] FROM [stored procedure]? Not FROM [Table] and without defining [temp table]? Select all data from BusinessLine into tmpBusLine works fine. select * into tmpBusLine from BusinessLine I am trying the same, but …

Read More
SQL query to select dates between two dates

I have a start_date and end_date. I want to get the list of dates in between these two dates. Can anyone help me pointing the mistake in my query. select Date,TotalAllowance from Calculation where EmployeeId=1 and Date …

Read More