Why does my yesterday query return no results?

Click here to monitor SSC

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>

SQLServerCentral is supported by Red Gate Software Ltd.

Log in :: Register :: Not logged in

<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>

Recent Posts

Popular Topics

Home

Home » SQL Server 2008 » T-SQL (SS2K8) » Why does my yesterday query return no...

Add to briefcase

13 posts, Page 1 of 2

1 2 »»

Why does my yesterday query return no results?

Expand / Collapse

Author

Message

Posted Tuesday, October 23, 2012 7:14 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, January 18, 2013 6:06 AM
Points: 99, Visits: 118

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

Post #1376022

Posted Tuesday, October 23, 2012 7:18 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, January 18, 2013 6:06 AM
Points: 99, Visits: 118

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

Post #1376024

Posted Tuesday, October 23, 2012 7:23 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:21 AM
Points: 5,602, Visits: 10,950

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
Exploring Recursive CTEs by Example Dwain Camps

Post #1376029

Posted Tuesday, October 23, 2012 7:26 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, January 18, 2013 6:06 AM
Points: 99, Visits: 118

Operand type clash: date is incompatible with int

Is it the time thats causing the problem ?

Post #1376031

Posted Tuesday, October 23, 2012 7:32 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, January 18, 2013 6:06 AM
Points: 99, Visits: 118

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 Smile

Smile

Post #1376040

Posted Tuesday, October 23, 2012 7:47 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:21 AM
Points: 5,602, Visits: 10,950

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 Smile

Smile

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
Exploring Recursive CTEs by Example Dwain Camps

Post #1376051

Posted Tuesday, October 23, 2012 7:50 AM

SSC Journeyman

Group: General Forum Members
Last Login: Friday, January 18, 2013 6:06 AM
Points: 99, Visits: 118

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

Operand type clash: date is incompatible with int

Post #1376054

Posted Tuesday, October 23, 2012 7:57 AM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:21 AM
Points: 5,602, Visits: 10,950

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
Exploring Recursive CTEs by Example Dwain Camps

Post #1376064

Posted Tuesday, October 23, 2012 9:08 AM

Right there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 4:56 AM
Points: 756, Visits: 2,298

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

Post #1376121

Posted Tuesday, October 23, 2012 8:43 PM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 6:14 PM
Points: 32,892, Visits: 26,763

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

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Post #1376303

« Prev Topic | Next Topic »

Add to briefcase

13 posts, Page 1 of 2

1 2 »»

Permissions

Expand / Collapse