Fetching all records for the previous day in SQL Server

Here's a query that lets you fetch all records for the previous day that falls between the time duration of 12 to 12

USE Northwind

DECLARE @PrevDate AS DateTime

SET @PrevDate = '1996-08-15'

SELECT OrderID, CustomerID, EmployeeID, OrderDate

FROM ORDERS

WHERE OrderDate < Cast(Convert(varchar,@PrevDate,101) as datetime)

and OrderDate >=  Cast(Convert(varchar,@PrevDate,101) as datetime) - 1

Results:

10280    BERGS    2    1996-08-14 00:00:00.000

10281    ROMEY    4    1996-08-14 00:00:00.000

About The Author

Suprotim Agarwal, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook