Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Get rows from today and yesterday

Posted on 2011-03-23
Medium Priority
Last Modified: 2012-05-11
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

Question by:Camillia
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
LVL 32

Expert Comment

by:Ephraim Wangoya
ID: 35200106
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

LVL 25

Expert Comment

by:Lee Savidge
ID: 35200110
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
LVL 13

Expert Comment

ID: 35200151
-- 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)
LVL 32

Accepted Solution

Ephraim Wangoya earned 1600 total points
ID: 35200180

You just need

where updatetdate >= DATEADD(dd, 0, DATEDIFF(dd, 0, DATEADD(DD, -1, GETDATE())))
LVL 13

Assisted Solution

Rick earned 400 total points
ID: 35200232
Or if you want to show yesterdays and todays together:

select * from table where [updatedate] between convert(varchar(20), dateadd(day, -1, getdate()), 101) and convert(varchar(20), getdate(), 101)

But ewangoya's solution is better (short and effective).

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question