Question:
I have a table with column called Date, which stores date record, that was entered in smalldatetime format (e.g., 2000-06-01 00:00:00).
I need to write a stored procedure that can be run on any given day, which will return records in the table that were entered the previous day (e.g., run the stored procedure on “2000-06-02” and return records with value in Date column of “2000-06-01 00:00:00”). Is there any way to use the GETDATE or DATEADD functions in some manner so the stored procedure will always return records entered the previous day?
Answer:
The following query uses DATEADD to add -1 to the current date and return the previous day. However, it returns the current date and time minus one day, so I use CONVERT to compare only the month/day/year. Otherwise, you would have to see if the DateCol value was greater than or less than a particular value:
SELECT DateCol FROM Test WHERE convert(varchar, DateCol, 1) = convert(varchar, (dateadd(dd, -1, getdate())), 1)
