Solved

Get rows from today and yesterday

Posted on 2011-03-23
5
704 Views
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

0
Comment
Question by:Camillia
  • 2
  • 2
5 Comments
 
LVL 32

Expert Comment

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

0
 
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
0
 
LVL 13

Expert Comment

by:Rick
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)
0
 
LVL 32

Accepted Solution

by:
ewangoya earned 400 total points
ID: 35200180

You just need

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

Assisted Solution

by:Rick
Rick earned 100 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).
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

708 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now