Problem with DATE

Question

Post reply

Problem with DATE

Shintu

SSC Eights!

Points: 803
More actions
May 26, 2010 at 11:36 pm

#219517

Hi ! ALL
I have a very small problem.
I have a table named dbo.Container, there are two colms in that .
1. Container_id
2. End_Journey_Date
Now I want to get Container_id of those who have ended there journey within 5 days w.r.t the date i will suppy.
I have made a query.
use IRISDW_AF;
select Container_Id,End_Journey_Date
from dbo.Container
where datediff(day,day('1/29/2009 12:00:00 AM'),day(End_Journey_Date))<=5;
here 1/29/2009 12:00:00 AM is the supplied date, Now I want the entry of only 5 days before this, as
the dates will be within 1/24/2009 to 1/29/2009,
In this query result there are showing 5 days entry for all the years ,i.e. 2007,2008 etc.
Can anyone modify this query? so that the query will only result 5 day entry for the perticular year which have been supplied.
Thanks
Subhro

Viewing 6 posts - 1 through 5 (of 5 total)

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

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 1

Subhro, try this

use IRISDW_AF;

select Container_Id,End_Journey_Date

from dbo.Container

where datediff(day,day('1/29/2009 12:00:00 AM'),day(End_Journey_Date))<=5

-- Filter with Datepart(year)

AND DATEPART(yy,'1/29/2009 12:00:00 AM') = DATEPART(yy,End_Journey_Date)

;

Tell us if that helped you!

ColdCoffee SSC-Dedicated Points: 39972 More actions · Answer 2

To learn more about DATEPART function, click on the following link.

Link : DATEPART (Transact-SQL)

hope this helps you!

C'est Pras!

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 3

Remove the DAY function like this

SELECTContainer_Id, End_Journey_Date

FROMdbo.Container

WHEREDATEDIFF( DAY, '1/29/2009 12:00:00 AM', End_Journey_Date ) <= 5

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Shintu SSC Eights! Points: 803 More actions · Answer 4

Thanks all of you for the suggestion .

I have myself made a small change LIKE :

use IRISDW_AF;

select Container_Id,End_Journey_Date

from dbo.Container

where datediff(day,'1/29/2009 12:00:00 AM',End_Journey_Date) BETWEEN 0 AND 5;

And it works

Thanks once more

Subhro

Paul White SSC Guru Points: 150467 More actions · Answer 5

You corrected the bug that ColdCoffee and Kingston missed, but all the solutions presented so far use a form which cannot seek using an index. The best they can do is to fully scan a suitable index.

To rewrite the query so it can use an index effectively:

DECLARE @Container

TABLE (

container_id INTEGER IDENTITY,

end_date DATE,

UNIQUE CLUSTERED (end_date)

);

INSERT @Container (end_date)

VALUES -- All dates 23 Jan 2009 to 5 Feb 2009 inclusive

('2009-01-23T00:00:00'),

('2009-01-24T00:00:00'),

('2009-01-25T00:00:00'),

('2009-01-26T00:00:00'),

('2009-01-27T00:00:00'),

('2009-01-28T00:00:00'),

('2009-01-29T00:00:00'),

('2009-01-30T00:00:00'),

('2009-01-31T00:00:00'),

('2009-02-01T00:00:00'),

('2009-02-02T00:00:00'),

('2009-02-03T00:00:00'),

('2009-02-04T00:00:00'),

('2009-02-05T00:00:00');

-- Clustered index scan and wrong result

SELECT C.container_id,

C.end_date

FROM @Container C

WHERE DATEDIFF(DAY, '2009-01-29T00:00:00', C.end_date) <= 5;

-- Index seek and correct results

SELECT C.container_id,

C.end_date

FROM @Container C

WHERE C.end_date BETWEEN '2009-01-29T00:00:00' AND DATEADD(DAY, 5, '2009-01-29T00:00:00');

Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi