Why does my yesterday query return no results?

Question

Post reply

Why does my yesterday query return no results?

jerome.morris

SSCrazy

Points: 2829
More actions
October 23, 2012 at 7:14 am

#283363

Hi all I have a datetime column in my DB and I am trying to fetch all records from yesterday using
SELECT *from SLADB.dbo.DocketTB
WHERE Docket_Date = (GETDATE() - 1)
but is always returns no results and I no the data is there
2012-09-22 21:33:39.390
is this because of the time ?
Thanks

Viewing 13 posts - 1 through 12 (of 12 total)

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

jerome.morris SSCrazy Points: 2829 More actions · Answer 1

where Docket_Date = DATEADD(d,DATEDIFF(d,0,GETDATE()),0)-1

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 2

Try casting both sides of the comparison as date:

WHERE CAST(Docket_Date AS DATE) = CAST(GETDATE() AS DATE) - 1

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

jerome.morris SSCrazy Points: 2829 More actions · Answer 3

Operand type clash: date is incompatible with int

Is it the time thats causing the problem ?

jerome.morris SSCrazy Points: 2829 More actions · Answer 4

SELECT *from SLADB.dbo.DocketTB

where Docket_EngFinish >=dateadd(d,datediff(d,0, getdate()),-1) AND Docket_EngFinish<dateadd(d,datediff(d,0, getdate()),0)

This works, is there a better way ?

Thanks again Chris 🙂

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

jerome.morris (10/23/2012)
SELECT *from SLADB.dbo.DocketTB
where Docket_EngFinish >=dateadd(d,datediff(d,0, getdate()),-1) AND Docket_EngFinish<dateadd(d,datediff(d,0, getdate()),0)
This works, is there a better way ?
Thanks again Chris 🙂

Yes - cast both sides as DATE

where CAST(Docket_EngFinish AS DATE) = CAST(GETDATE() AS DATE) - 1

Does Docket_EngFinish have a time component or is it all 0's?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

jerome.morris SSCrazy Points: 2829 More actions · Answer 6

It has a time stamp also and the cast gave me the error

Operand type clash: date is incompatible with int

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 7

Oops...

CAST(GETDATE() -1 AS DATE)

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

kupy SSCommitted Points: 1652 More actions · Answer 8

Hi

Did you try DATEDIFF(day, Docket_EngFinish , getdate())=1 ?

The datediff by day checks only the day part and not the time.

http://msdn.microsoft.com/en-us/library/ms189794.aspx

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 9

ChrisM@Work (10/23/2012)
Yes - cast both sides as DATE

I don't believe so, Chris... no chance of getting an index seek that way.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 10

jerome.morris (10/23/2012)
It has a time stamp also and the cast gave me the error
Operand type clash: date is incompatible with int

Wait a minute now... are you saying that one of the date columns is an INT that looks like a date?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

Jeff Moden (10/23/2012)
jerome.morris (10/23/2012)
It has a time stamp also and the cast gave me the error
Operand type clash: date is incompatible with int
Wait a minute now... are you saying that one of the date columns is an INT that looks like a date?

Jeff, you can add an INT to a DATETIME but not a DATE.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 12

Jeff Moden (10/23/2012)
ChrisM@Work (10/23/2012)
Yes - cast both sides as DATE
I don't believe so, Chris... no chance of getting an index seek that way.

That's what I thought too until recently.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden