Solved

SQL Server - Query syntax for yesterday's date

Posted on 2010-11-08
6
665 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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 25

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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

707 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now