Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 746
  • Last Modified:

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')
0
spirose
Asked:
spirose
1 Solution
 
Nathan RileyFounder/CTOCommented:
SELECT DATEADD(day, -1, getdate())
0
 
vdr1620Commented:
try this


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

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

Open in new window

0
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now