CODEWITHSUNDEEP
c#sqlsql-server-2008date
WillIAM
WillIAM

Reputation: 123

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

What I need IS:

2011-03-09

Upvotes: 12

Views: 34047

Answers (4)

Dylan Beattie
Dylan Beattie

Reputation: 54160

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.

Upvotes: 9

iivel
iivel

Reputation: 2576


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

Upvotes: 2

Joe Stefanelli
Joe Stefanelli

Reputation: 135858

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.

Upvotes: 9

Kris Ivanov
Kris Ivanov

Reputation: 10598

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

Upvotes: 1

Related Questions