SQL Query (data for yesterday)

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 

Select all Open in new window

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);

Select all Open in new window

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

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 

Select all Open in new window

modified

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

@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

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.

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)

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 :-).

I see, thanks. My solution doesn't limit just to yesterday. It includes since yesterday.

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.

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.]

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')

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.

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.

@Scott

Thanks: " a self-contained query ..." does have merit.

mmm, others made more contribution than I did

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

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

As a topic advisor I can "unaccept" this question so you may do it again. OK?