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

Question

Here is What I have So Far

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

YIELDS

2011-03-10 13:46:00

What I need IS:

2011-03-09

Answer 1

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.