getting the rows of dates near today date

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

getting the rows of dates near today date

Author

Topic

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-02-07 : 16:47:40

for example i have
1/2/2004
5/2/1955
30/2/1978

and if today for example is 7/2/2006
then i want to get all the rows with the date (by date i mean day+month)
that are in the range of +-3 days from todays date(day+month)
in this case i will recive only the row with 5/2/1955
how do i do this?
thnaks in advance
peleg

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-02-07 : 16:54:24

U can get the day and month seperately and have a hypothetical year as 2000
Then create all days as mm/dd/2000

Then do the comparison by datediff

SamC
White Water Yakist

3467 Posts

Posted - 2006-02-07 : 17:01:00

quote:
Originally posted by pelegk2

then i want to get all the rows with the date (by date i mean day+month)
that are in the range of +-3 days from todays date(day+month)

SELECT MyDate
  FROM MyTable
  WHERE DATEPART(dy, MyDate) BETWEEN DATEPART(dy, GETDATE())-3 AND DATEPART(dy, GETDATE())+3

Got to work out the kinks when today is Jan 1...3 or Dec 29...31

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-02-07 : 17:02:08

ok i have tried



SELECT     datepart(day,deceased_date),
datediff(day,datepart(day,getdate()),datepart(day,deceased_date)+'/'+datepart(month,deceased_date)+'/'+year(getdate()))
FROM         izkur 
where datepart(month,deceased_date)>=month(getdate())

but i recive error on the "/"
what to do?

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-02-07 : 17:03:18

SamC not only that dont foget 29/2 :):)

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

SamC
White Water Yakist

3467 Posts

Posted - 2006-02-07 : 17:09:08

quote:
Originally posted by pelegk2

SamC not only that dont foget 29/2 :):)

Feb 29 should be accounted for by DATEPART(dy...

You could brute force this with 7 WHERE conditions"


WHERE
      DATEPART(dy, Mydate) = DATEPART(dy, GETDATE()-3)  
  OR  DATEPART(dy, Mydate) = DATEPART(dy, GETDATE()-2)  
  OR  DATEPART(dy, Mydate) = DATEPART(dy, GETDATE()-1)  
  OR  DATEPART(dy, Mydate) = DATEPART(dy, GETDATE()-0)  
  OR  DATEPART(dy, Mydate) = DATEPART(dy, GETDATE()+1)  
  OR  DATEPART(dy, Mydate) = DATEPART(dy, GETDATE()+2)  
  OR  DATEPART(dy, Mydate) = DATEPART(dy, GETDATE()+3)

SamC
White Water Yakist

3467 Posts

Posted - 2006-02-07 : 17:13:01

Maybe get slick with IN below, but I suspect there's an elegant solution up someone's sleeve.


WHERE
  DATEPART(dy, Mydate) IN
    (SELECT DATEPART(dy, GETDATE() + 3)
     UNION ALL
     SELECT DATEPART(dy, GETDATE() + 2)
     UNION ALL
     SELECT DATEPART(dy, GETDATE() + 1)
     UNION ALL
     SELECT DATEPART(dy, GETDATE() + 0)
     UNION ALL
     SELECT DATEPART(dy, GETDATE() - 1)
     UNION ALL
     SELECT DATEPART(dy, GETDATE() - 2)
     UNION ALL
     SELECT DATEPART(dy, GETDATE() - 3)
    )

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-02-07 : 17:18:02

i tried this


SELECT     
datediff(day,datepart(day,getdate()),convert(datetime,datepart(day,deceased_date)+'-'+datepart(month,deceased_date)+'-'+year(getdate()))) as delta,*,convert(datetime,datepart(day,deceased_date)+'-'+datepart(month,deceased_date)+'-'+year(getdate()))
FROM         izkur 
where datediff(day,datepart(day,getdate()),convert(datetime,datepart(day,deceased_date)+'-'+datepart(month,deceased_date)+'-'+year(getdate())))>=-3
or  datediff(day,datepart(day,getdate()),convert(datetime,datepart(day,deceased_date)+'-'+datepart(month,deceased_date)+'-'+year(getdate())))<=3

but i got very strange results on the date,instead of this yeat i got 1905!!!

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-02-07 : 17:23:17

ok this is the more elegent solution :


SELECT     *,
datepart(dy,deceased_date),datepart(dy,getdate())
FROM         izkur 
where datepart(dy,getdate())-3<=datepart(dy,deceased_date)
and datepart(dy,getdate())+3>=datepart(dy,deceased_date)

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-02-08 : 04:43:32

[code]select *
from table
where date between dateAdd(day, -3, dateAdd(day, dateDiff(day, 0, getDate()), 0)) and dateAdd(day, 3, dateAdd(day, dateDiff(day, 0, getDate()), 0))[/code]If you have an index on date, this is the most efficient solution.

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-02-08 : 11:58:39

quote:
If you have an index on date, this is the most efficient solution

can u explain?

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-02-09 : 07:03:24

Sure. This is the only sargable query among all solution offered in the thread. If you have index on that date column, the first value calculated will be used to find first row (if any) that satisfies condition date >= the first. Then, index entry will be read sequentially as long as current date value <= the second value. For each row found bookmark lookup will be done unless the index starting with date covers all columns returned or it is an clustered index. Other queries would scan the table or the index (if it is covering or clustered index) and test each date against the criteria. So other queries will read all entries in the index while sargable query (like one I proposed) would read just rows that match the criteria.

pelegk2
Aged Yak Warrior

723 Posts

Posted - 2006-02-13 : 01:59:32

i appologize but i didnt understand execlly all the explenation, and can u explainexeclly what "clustered" option which i see onthe SQL SERVER means?

Israel -the best place to live in aftr heaven 9but no one wan't to go there so fast -:)

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-02-13 : 05:31:03

Clustered index has rows in leaf nodes instead of pointers to data rows. More in BOL.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources