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