SQL-Date-Question: How to get Yesterdays date in the following formatte

Here is What I have So Far

declare @Today smalldatetime
Set @Today = GETDATE()
select @Today

YIELDS

2011-03-10 13:46:00

开发者_StackOverflowWhat I need IS:

2011-03-09

Try this:

SELECT REPLACE(CONVERT(VARCHAR, DATEADD(dd, -1, GETDATE()), 102), '.', '-')

GETDATE() returns the current date/time.

DATEADD(dd, -1, GETDATE()) substracts one day from the current date/time.

CONVERT(VARCHAR, @DATE, 102) converts the date to ANSI format yyyy.mm.dd

and the REPLACE will replace the periods in the predefined format with hyphens as per your example.

For 2008 you can take advantage of the new DATE datatype:

SELECT CAST(DATEADD(d,-1,GETDATE()) AS DATE) AS Yesterday

For all versions:

SELECT CONVERT(CHAR(10), DATEADD(d,-1,GETDATE()), 120) AS Yesterday

Obviously, the datatype returned by each method is different.

SELECT CONVERT(varchar, DATEADD(d,-1,GETDATE()), 110) 

or

SELECT CAST(DATEADD(d,-1,GETDATE()) AS DATE) AS 'DATE' 

Good reference if you ever need those codes again. http://www.w3schools.com/sql/func_convert.asp

SELECT CONVERT(VARCHAR, DATEADD(d,-1,GETDATE()), 110) AS Yesterday