?
Solved

SQL Server - Query syntax for yesterday's date

Posted on 2010-11-08
6
Medium Priority
?
748 Views
Last Modified: 2012-05-10
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')
0
Comment
Question by:spirose
6 Comments
 
LVL 12

Expert Comment

by:Nathan Riley
ID: 34085855
SELECT DATEADD(day, -1, getdate())
0
 
LVL 16

Accepted Solution

by:
vdr1620 earned 500 total points
ID: 34085858
try this


SELECT CONVERT(VARCHAR(10),DATEADD(D,-1,GETDATE()),112)
0
 
LVL 6

Expert Comment

by:dan_mason
ID: 34085859

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

Open in new window

0
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
LVL 27

Expert Comment

by:Shaun Kline
ID: 34085868
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
 
LVL 6

Expert Comment

by:dan_mason
ID: 34085870
Sorry, ignoreme - you need the DATEADD as per vdr1620. I'd still go varchar(8) though...
0
 
LVL 6

Expert Comment

by:dan_mason
ID: 34085911
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

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Article by: Tammy
MySQLTuner is a script written in Perl that allows you to review a MySQL installation quickly and make adjustments to increase performance and stability. The current configuration variables and status data is retrieved and presented in a brief forma…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…

600 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question