Member Avatar for 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?

  • 3 Contributors
  • 3 Replies
  • 227 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by axman1000
Member Avatar for 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)
Edited by debasisdas because: n/a
Member Avatar for 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())
commented: thank you for providing alternate solution. +8
Member Avatar for axman1000 
(SELECT CONVERT(VARCHAR(10),DATEADD(DD, DATEDIFF(DD, 0, GETDATE()), -1),120))

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.