How to retrieve the previous date and use it in a query?

[axman1000]

I would like to retrieve the previous date (i.e., current date minus 1) and use it in a query through an = operator.

I got the following query to retrieve the previous date: select DATEADD(DD, DATEDIFF(DY, 0, GETDATE()), -1) It gives: 2011-03-14 00:00:00.000

I would like to get only: 2011-03-14, and use this in a query to check against a column's data.

Something like: select * from table where column1 in ('values list') and date=(previous_date) How can I combine the two and get it in the format I want?

[debasisdas]

Try this.

SELECT CAST((STR( YEAR( @date ) ) + '-' +STR( MONTH( @date ) ) + '-' +STR( DAY(@date ) )
) AS DATE_AS_I_WANT

or this

SELECT CONVERT(varchar, @date,112)

[buddylee17]

This will work with 2008. With 2005 or earlier, you'll need to use datetime and the convert to varchar to strip the time portion off.

DECLARE @yesterday DATE
SET @yesterday = DATEADD(D,-1,GETDATE())

[axman1000]

(SELECT CONVERT(VARCHAR(10),DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), -1),120))

This is what I needed.. Thanks for your help though, guys. :)