asked on

Get rows from today and yesterday

This is SQL 2000

I have a table with rows dated today and prior to today. I want to get the rows dated today and yesterday. I tried the 2 below sqls and I dont get any rows back:

select  * from table where  convert(varchar(10),updatedate, 101) >= convert(varchar(10),getdate(), 101)
 and convert(varchar(10),updatedate, 101) <= convert(varchar(10),getdate() -1 , 101)
/*
 where   DATEADD(dd, 0, DATEDIFF(dd, 0, updatedate)) >= DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
 and DATEADD(dd, 0, DATEDIFF(dd, 0, updatedate)) <= DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()- 1))
*/
 order by updatedate desc

Open in new window

Ephraim Wangoya

try

where updatedate >= DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))
or updatedate > DATEADD(dd, 0, DATEDIFF(dd, 0, DATEADD(DD, -1, GETDATE())))

Open in new window

Lee

don't convert anything.

select * from mytable where updatedate > getdate() - 2 order by updatedate desc

That will give you everything updated in the last 2 days but include the time in that. If you don't want the time element included:

select * from mytable where cast(convert(nvarchar(10), updatedate, 101) as datetime) > getdate() - 2order by updatedate desc

Rick

-- Today's:
select * from table where convert(varchar(20), [updatedate], 101) = convert(varchar(20), getdate(), 101)

-- Yesterday's:
select * from table where convert(varchar(20), [updatedate], 101) = convert(varchar(20), dateadd(day, -1, getdate()), 101)

ASKER CERTIFIED SOLUTION

Ephraim Wangoya

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Rick

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial