dcsimg
Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
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

WEBINAR:

On-Demand

Building the Right Environment to Support AI, Machine Learning and Deep Learning


Querying Date Fields

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)
DevX Pro
 
Comment and Contribute

 

 

 

 

 


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

 

 

Sitemap
Thanks for your registration, follow us on our social networks to keep up-to-date