SQL Server - Query syntax for yesterday's date

How do I Parse yesterday's date so that it shows up as 20101107 (yyyymmdd)
(Note that if the day or month is a single digit number, it should be preceded by a zero '0')
spiroseAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
vdr1620Connect With a Mentor Commented:
try this


SELECT CONVERT(VARCHAR(10),DATEADD(D,-1,GETDATE()),112)
0
 
Nathan RileyFounderCommented:
SELECT DATEADD(day, -1, getdate())
0
 
dan_masonCommented:

SELECT CONVERT(VARCHAR(8), GETDATE()-1, 112) AS [YYYYMMDD]

Open in new window

0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Shaun KlineLead Software EngineerCommented:
Try:

SELECT CONVERT(VARCHAR(8), DATEADD(day, -1, GETDATE()), 112) AS [YYYYMMDD]

(found here: http://www.sql-server-helper.com/tips/date-formats.aspx)
0
 
dan_masonCommented:
Sorry, ignoreme - you need the DATEADD as per vdr1620. I'd still go varchar(8) though...
0
 
dan_masonCommented:
Sorry to mess this thread up totally, but actually my 'correction' was wrong: originally-posted query works fine. I would imagine GETDATE()-1 is very marginally quicker than using the DATEADD; main advantage in my view though is that it's just easier to read.
SELECT CONVERT(VARCHAR(8), GETDATE()-1, 112) AS [YYYYMMDD]
 

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.