Avatar of isames
isames
 asked on

SQL Query (data for yesterday)

i have a column called RentDate.

I want to get all info that happened the previous day, no matter what time it happened the previous day.

So no matter when i run the query, it need to show me the previous day info.

The query below gives me the correct info, but I know I could do something different for RentDate:

select customernbr, storenbr, toolnbr, count(toolnbr)
  from ToolsOnRent
  where RentDate '2015-07-29 22:00:21.427'
  and StoreNbr = '01101' and customernbr = '02001020'
  group by CustomerNbr, storenbr, toolnbr
Microsoft SQL ServerMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
PortletPaul

8/22/2022 - Mon
Ryan Chong

try:

select customernbr, storenbr, toolnbr, count(toolnbr)
  from ToolsOnRent
  where convert(varchar(10), RentDate , 112) = convert(varchar(10), getdate()-1, 112)
  and StoreNbr = '01101' and customernbr = '02001020'
  group by CustomerNbr, storenbr, toolnbr 

Open in new window

ste5an

E.g.

DECLARE @Sample TABLE ( RentDate DATETIME );

INSERT  INTO @Sample
VALUES  ( GETDATE() ),
        ( GETDATE() - 1 ),
        ( GETDATE() - 2 ),
        ( '20150729 22:00:21.427' );

SELECT  *
FROM    @Sample S
WHERE   S.RentDate > CAST(GETDATE() - 1 AS DATE)
        AND S.RentDate < CAST(GETDATE() AS DATE);

Open in new window


Ryan's suggestion is using a non-sargable predicate. Thus no index seek on RentDate is possible.
Mike Eghtebas

select customernbr, storenbr, toolnbr, count(toolnbr)
  from ToolsOnRent
  where RentDate > cast((getdate()-1) as date)
  and StoreNbr = '01101' and customernbr = '02001020'
  group by CustomerNbr, storenbr, toolnbr 

Open in new window



modified
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Scott Pletcher

Date or datetime ranges should always be >= and <, not just for "sargability" but also for accuracy.

I use a CROSS APPLY just to able to assign a meaningful name to the calculated value.

from ToolsOnRent
cross apply (
    SELECT DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, 0) AS yesterday_midnight
) AS assign_alias_names

where RentDate >= yesterday_midnight
and RentDate < DATEADD(DAY, 1, yesterday_midnight)

and StoreNbr = '01101' and customernbr = '02001020'
group by ...

A few other quick examples of date ranges:
>= '20150701' AND < '20150801' --an entire month, in this case July 2015
>= '20140101' AND < '20150101' --an entire year, in this case 2014
Mike Eghtebas

@Scott,

cast((getdate()-1) as date)   produces mm-dd-yyyy for yesterday.

Yesterday dates are either mm-dd-yyyy or have some time elements.

For your consideration, therefore:

where RentDate >= cast((getdate()-1) as date)

Should do it. Is this correct? Note that I have changed > with >=.

Thanks,

Mike
Scott Pletcher

I don't think so.  I assume data from today could be in the table, and they only want to see yesterday's data, thus still need an end range.

st35an already had the CAST(... as date) method, but I personally don't care for that as much if the table column itself is a datetime.  It's not a performance thing, just a clarity and maintenance thing, for me at least.  I could see them coming along at some time and treating a "day" as, say, from 6AM one morning to <6AM the next; if you're changing code using "date" only, that will be a much harder change to make.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Mike Eghtebas

My apologies to st35an, I saw his post after submitting mine. Here in fact between is implied

Between  cast((getdate()-1) as date) And <Tomarrow that will never exist>

So because tomorrow is out of picture, there is no need to include it. So the following seems to be sufficient. Then again I have lot to learn yet hence asking about it:

where RentDate >= cast((getdate()-1) as date)
Scott Pletcher

You're running this on, say, July 30.  Yesterday is July 29.  You want to see activity only for July 29.  But you will see activity from July 30 if your WHERE condition is just ">= July 29", since july 30 is greater than july 29 :-).
Mike Eghtebas

I see, thanks. My solution doesn't limit just to yesterday. It includes since yesterday.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
PortletPaul

I suspect ste5an missed an equals symbol in line 11, I think he intended

WHERE   S.RentDate >= CAST(GETDATE() - 1 AS DATE) --<< an equal symbol added
        AND S.RentDate < CAST(GETDATE() AS DATE);

This is both sargable and accurate for "yesterday"

Although if  [RentDate] is datetime then you are comparing datetime to date, which is fine, it is allowed, but you could avoid the implied conversion by using a datediff/dateadd calculation that returns datetime (which Scott has used)

DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, 0)  --< <sets time to 00:00:00 & returns a datetime

so the equivalent sargable predicate with datetime to datetime comparisons is:

WHERE   S.RentDate >= DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, 0)
        AND S.RentDate < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) , 0)

I don't see how using a cross apply adds value or makes it easier to maintain.  It would be an unusual use of cross apply in my experience. It's not that I disagree with using them for alias re-usability its just I wouldn't use them for a constant, seems unwarranted. A more common approach would be use of a @local_variable

declare @yesterday as datetime
set @yesterday  = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, 0)
select
...
WHERE   S.RentDate >= @yesterday
        AND S.RentDate < DATEADD(DAY, 1, @yesterday )

----------
One thing I most definitely would not do and that is convert RentDate to varchar.
From a performance perspective this is just not a good approach.
Scott Pletcher

I don't see how using a cross apply adds value or makes it easier to maintain.

OK, try adjusting both sets of code to define a day as from 5AM one day to 5AM the next day instead of from midnight.

 [And keep in mind that self-documenting code is better than comments, when applicable.  For example, it's better to name variable @invoice_total than to name it @i and then have a comment that says that @i contains the invoice total.]
PortletPaul

While a redefinition of 'yesterday' might have occurred somewhere at some time, I'm not sure I could ever protect all code from such "edge cases"

But  I don't see how a cross apply is more self documenting or simpler to update than a local variable, e.g.

set @yesterday  = DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, '05:00:00')
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Scott Pletcher

In some cases you can't use variables, in others you simply want a self-contained query, with no variable dependencies.

Don't see the most critical part of this query -- defining what date range it will retrieve -- is an "edge case".  Almost anytime such a calc would appear more than once I prefer to assign it a name.  Sure, with this short and simple a query, it's (perhaps?) easy to see everywhere it should be changed, but on a longer, more complex query that could be considerably more difficult, esp. if other date calcs are being done.

Similarly, if you're computing, say, sales tax, you really want to add a variable or name for:
DECLARE @sales_tax_rate = 0.0650 --6.5%
rather than hard-coding ".065" everywhere, because you know the sales tax rate can change at some point.
isames

ASKER
So I tried a couple of these suggestions in my query and have been getting the right info for two days now. Thanks!!!

DATEADD(datepart,number,date) : I understand this allows you to add or subtract from a date. It returns a date.

DATEDIFF(datepart,startdate,enddate): i understand that this gives you the difference between two dates. It returns an integer.

Questions:

1. When I type select DATEDIFF(DAY, 0, GETDATE()) - 1, I get 42213, which is 42213 days since datepart is Day. If startdate is 0 and getdate() is current date, how is it returning 42213?

2. So I tested that Select DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, 0) and Select DATEADD(DAY, 42213, 0) gives the same date. So it seems this is adding 42213 days to 0, which is in the DATE position of the function. How is that returning yesterday's date. I know I don''t understand what 0 means in the DATE.
ASKER CERTIFIED SOLUTION
PortletPaul

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
PortletPaul

@Scott

Thanks: " a self-contained query ..." does have merit.
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
PortletPaul

mmm, others made more contribution than I did
isames

ASKER
@paul maxwell
not sure if i can take it back.

I should've opened a second ticket for explanation date being 0
PortletPaul

As a topic advisor I can "unaccept" this question so you may do it again. OK?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.