Login | Register   
RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX

By submitting your information, you agree that devx.com may send you DevX offers via email, phone and text message, as well as email offers about other products and services that DevX believes may be of interest to you. DevX will process your information in accordance with the Quinstreet Privacy Policy.

Tip of the Day
Language: SQL Server
Expertise: Beginner
Jun 12, 2000



Application Security Testing: An Integral Part of DevOps

Querying Date Fields

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:

FROM Test 
WHERE convert(varchar, DateCol, 1) = convert(varchar, (dateadd(dd, -1, getdate())), 1)
DevX Pro
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



We have made updates to our Privacy Policy to reflect the implementation of the General Data Protection Regulation.
Thanks for your registration, follow us on our social networks to keep up-to-date