Solved

SQL Server - Query syntax for yesterday's date

Posted on 2010-11-08
6
686 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 11

Expert Comment

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

Accepted Solution

by:
vdr1620 earned 125 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 26

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
How to set UNMASK permission only to only one table in SQL Server 2016 ? 11 75
SQL: launch actions one before the other 10 22
sql 2014,  lock limit 5 29
Sql server insert 13 27
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

785 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