Return all rows for todays date, and yesterday.

Question

Post reply

Return all rows for todays date, and yesterday.

Trystan Hughes

SSCrazy

Points: 2109
More actions
October 17, 2006 at 6:06 am

#114707

Hi all,
I have searched the forum but to no avail.
Whats the best way for me to return all rows that have a datetime for today and yesterday, where yesterday can go back to being from a previous month?
Thanks
Tryst

Viewing 15 posts - 1 through 15 (of 27 total)

You must be logged in to reply to this topic. Login to reply

Sergiy SSC Guru Points: 110208 More actions · Answer 1

Does "today" go back to prvious month with "yesterday"?

_____________
Code for TallyGenerator

David Burrows SSC Guru Points: 65143 More actions · Answer 2

Just semantics but how can yesterday be from last month

Two possible interpretations I can see from your request

1.

DECLARE @today datetime, @yesterday datetime

SET @today = '20061017'

SET @yesterday = '20060916'

SELECT [column]

FROM

WHERE [date] >= @yesterday

AND [date] <= @today+1

2.

DECLARE @today datetime, @yesterday datetime

SET @today = '20061017'

SET @yesterday = '20060916'

SELECT [column]

FROM

WHERE ([date] >= @today AND [date] <= @today+1)

OR ([date] >= @yesterday AND [date] <= @yesterday+1)

Far away is close at hand in the images of elsewhere.
Anon.

Vladan SSC-Insane Points: 22072 More actions · Answer 3

"Just semantics but how can yesterday be from last month?"

Well, I suppose it was meant as "if today is the first of a month, yesterday is the last day from previous month". Trystan probably wanted to avoid solutions based on subtracting 1 from the day number.

I would suggest this solution, which also takes care of any possible time portions in the date:

SELECT col1, col2

FROM tbl

WHERE tbl.date >= DATEADD(d, DATEDIFF(d, 1, GETDATE()), 0) /*date is greater or equal to yesterday*/

AND tbl.date < DATEADD(d, DATEDIFF(d, -1, GETDATE()), 0) /*date is less than tomorrow*/

If you need to parametrize it (using some other date instead of actual as a "today" date), just replace GETDATE() with a variable.

David Burrows SSC Guru Points: 65143 More actions · Answer 4

Good catch Vladan

Obvious when someone shows you how

Far away is close at hand in the images of elsewhere.
Anon.

Cory E. SSCertifiable Points: 7076 More actions · Answer 5

why have the datediff and dateadd? Would it be easier to just do something like this:

declare

@offset int

set

@offset = 1

select

* from tbl

where tbl.datefield > getdate()-@offset

C

-- Cory

Trystan Hughes SSCrazy Points: 2109 More actions · Answer 6

"Well, I suppose it was meant as "if today is the first of a month, yesterday is the last day from previous month". Trystan probably wanted to avoid solutions based on subtracting 1 from the day number."

Exactly that, Vladan

Ok, will check some of these out, but as its to be run on SQL Mobile, the use of variables must be avioded.

Thanks.

Tryst

Trystan Hughes SSCrazy Points: 2109 More actions · Answer 7

Ok, I am going to throw a spanner in the works here, as it seems that I need todays date (as in my orginal requirement) but then need the next latest date that resides in the table.

So display all entries/rows that have been created today, and also all other rows that the last day in which rows were entered into the database. This fixes the issue for scenarios where I would run this on a Monday, which would be get all entries for today, and then also get the next last day in which rows where entered, which would be the Friday (poss sat) - but if the queries works, it wouldn't matter what day it was as it would return all those for either Friday or Sat.

(i'm using SQL Mobile, so variables, and Sub Queries are not allowed, apart from in the IN clause).

(I am guessing I may need to perform more than one query to get this type of data??)

Tryst

Vladan SSC-Insane Points: 22072 More actions · Answer 8

No, it wouldn't. That is, it may be easier, but it will not work properly.

GETDATE() returns for example 2006-10-17 16:34:27. DATEADD/DATEDIFF are there mainly to cut off the time portion, and I tend to use them anywhere, where I have to compare some dates. Even if it is just to pull data from today, without any offset.

That DATEADD allows to manipulate the date as required, to get yesterday, today and tomorrow, is fortunate and IMHO this is the easiest way to do it - although of course, there several ways to get at the same result.

Generally, when working with datetime data in SQL Server, you always have to remember the time portion (and also the fact that time portion is ALWAYS part of the date - even if it is 00:00:00, which means midnight, i.e. first second of the new day). If you don't strip away time portion, you will get rows that were entered not during yesterday and today, but from 2006-10-16 16:34:27 to now (using the above GETDATE() result). You want to have there all rows from yesterday.

Cory E. SSCertifiable Points: 7076 More actions · Answer 9

Thanks for the explaination. Another way to look at this may be that if you want everything from today and yesterday, you need the DATAADD/DATEDIFF. If you want everything for the past 24 hours from now, you need the getdate()-1. A fair amt of the the stuff I have done needs just 24 hours.

Thanks!

-- Cory

Vladan SSC-Insane Points: 22072 More actions · Answer 10

That's precisely what I was trying to put across - it depends on what you need. I probably never needed anything for last 24 hours . Most of the stuff I do requires data from a certain day (or day range).

Trystan Hughes SSCrazy Points: 2109 More actions · Answer 11

I have managed to get this to work by using the following query (quite messy query )

SELECT*

FROMtbl_NSP_Inspection

WHERE((CreatedDate >= DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0)) AND (CreatedDate < DATEADD(d, DATEDIFF(d, -1, GETDATE()), 0)))

OR(SUBSTRING(CONVERT(NVARCHAR(20), CreatedDate, 21), 1, 10) IN (SELECTSUBSTRING(CONVERT(NVARCHAR(20), MAX(CreatedDate), 21), 1, 10)

FROMtbl_NSP_Inspection

WHERE(DATEPART(DAY, CreatedDate) != DATEPART(DAY, GETDATE()))

OR(DATEPART(MONTH, CreatedDate) != DATEPART(MONTH, GETDATE()))

OR(DATEPART(YEAR, CreatedDate) != DATEPART(YEAR, GETDATE()))

))

Tryst

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 12

Trystan,

Try this for your where clause:

dateadd

(d,datediff(d,0,CreatedDate),0) in (select top 2 dateadd(d,datediff(d,0,CreatedDate),0) from tbl_NSP_Inspection order by TestDate desc)

Vladan SSC-Insane Points: 22072 More actions · Answer 13

I don't know anything about limitations of Mobile SQL, but to hold to the style with which you started, it would be better to write it this way:

SELECT col1, col2, ...

FROM tbl_NSP_Inspection i

WHERE ((i.CreatedDate >= DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0))

AND (i.CreatedDate < DATEADD(d, DATEDIFF(d, -1, GETDATE()), 0)))

OR DATEADD(d, DATEDIFF(d, 0, i.CreatedDate), 0) =

(select DATEADD(d, DATEDIFF(d, 0, max(i2.CreatedDate)), 0)

from tbl_NSP_Inspection i2

where i2.CreatedDate < DATEADD(d, DATEDIFF(d, 0, GETDATE()), 0))

If you wonder why I'm using aliases everywhere, look here:

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=315200

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 14

Sorry, missed something in my testing, this is what you should try:

dateadd(d,datediff(d,0,CreatedDate),0) in (select distinct top 2 dateadd(d,datediff(d,0,CreatedDate),0) from tbl_NSP_Inspection order by dateadd(d,datediff(d,0,CreatedDate),0) desc)

Also noticed I forgot to change one other column name in my previous post.