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
LVL 1
isamesAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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

ste5anSenior DeveloperCommented:
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 EghtebasDatabase and Application DeveloperCommented:
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
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Scott PletcherSenior DBACommented:
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 EghtebasDatabase and Application DeveloperCommented:
@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 PletcherSenior DBACommented:
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.
Mike EghtebasDatabase and Application DeveloperCommented:
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 PletcherSenior DBACommented:
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 EghtebasDatabase and Application DeveloperCommented:
I see, thanks. My solution doesn't limit just to yesterday. It includes since yesterday.
PortletPaulEE Topic AdvisorCommented:
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 PletcherSenior DBACommented:
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.]
PortletPaulEE Topic AdvisorCommented:
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')
Scott PletcherSenior DBACommented:
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.
isamesAuthor Commented:
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.
PortletPaulEE Topic AdvisorCommented:
>>"I don''t understand what 0 means in the DATE."
In the case of SQL Server the "zero date" is 1st January 1900 or '19000101'

these are the same:
DATEDIFF(day, 0, getdate() )
DATEDIFF(day, '19000101', getdate() )

DATEDIFF(day,0, ...)  is returning the number of whole days since 1900-01-01 and the other date

--- also
While it may come as a surprise, both dates and times ARE inherently numeric. Consider that we can say "I'll see you in 10 days" or "in 12 hours from now" and then work out what that means. In language we are often adding/subtracting intervals to/from dates. So your database also needs features to do the same operations, and hence dates/times ARE stored that way in the database. SQL Server isn't alone in doing this, most relational databases do and Excel (for example) handles dates/times as a decimal number, with the integer portion representing the days and the decimal portion representing time within the day.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulEE Topic AdvisorCommented:
@Scott

Thanks: " a self-contained query ..." does have merit.
PortletPaulEE Topic AdvisorCommented:
mmm, others made more contribution than I did
isamesAuthor Commented:
@paul maxwell
not sure if i can take it back.

I should've opened a second ticket for explanation date being 0
PortletPaulEE Topic AdvisorCommented:
As a topic advisor I can "unaccept" this question so you may do it again. OK?
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.