Between Datetime But I Want To Ignore Year

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 2005 Forums

Transact-SQL (2005)

Between Datetime But I Want To Ignore Year

Author

Topic

LacOniC
Starting Member

29 Posts

Posted - 2010-11-26 : 08:59:10

I have a column that is named as DATE.

I want to find records between today and after 45 days but i want to ignore the year. For example i want to find records BETWEEN 26.11 AND 10.01.

ROW 1 DATE : 30.11.2008
ROW 2 DATE : 05.01.2006
ROW 3 DATE : 05.08.2000

So i want to get ROW 1 AND 2. I tried some functions like dayofyear of datepart but new year or month was a problem. Is there any advice?

Thanks in advance.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-26 : 09:02:30

where right(convert(varchar(8),det,112),4) between '1126' and '0110'

where right(convert(varchar(8),det,112),4) between right(convert(varchar(8),getdate(),112),4) and right(convert(varchar(8),dateadd(dd,getdate(),45),112),4)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-11-26 : 10:01:54

quote:
Originally posted by nigelrivett

where right(convert(varchar(8),det,112),4) between '1126' and '0110'

where right(convert(varchar(8),det,112),4) between right(convert(varchar(8),getdate(),112),4) and right(convert(varchar(8),dateadd(dd,getdate(),45),112),4)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

I don't think the above solution will work...

But maybe this:

declare @test table (id int identity(1,1), datecol datetime)
insert @test
select '20081130' union all
select '20060105' union all
select '20000805'

select
*
from @test
where datepart(dayofyear,datecol) >= datepart(dayofyear,'19001126')
   or datepart(dayofyear,datecol) <= datepart(dayofyear,'19000110')

No, you're never too old to Yak'n'Roll if you're too young to die.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-26 : 10:11:07

Think both have problems.
One works if the two dates are in the same year the other if the second is in the following year.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-26 : 10:24:24

maybe
select
*
from @test
where
((datepart(dayofyear,datecol) >= datepart(dayofyear,@d1) or datepart(dayofyear,datecol) <= datepart(dayofyear,@d2))
and datediff(@d1,@d2) = 1
)
or
((datepart(dayofyear,datecol) >= datepart(dayofyear,@d1) and datepart(dayofyear,datecol) <= datepart(dayofyear,@d2))
and datediff(@d1,@d2) = 0
)

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

LacOniC
Starting Member

29 Posts

Posted - 2010-11-29 : 05:19:21

Thank you all for your ideas. I used:

SELECT *
FROM Table1
WHERE
(
((substring(convert(varchar(10),getdate()+45,112),1,4)>substring(convert(varchar(10),getdate(),112),1,4))
and ((substring(convert(varchar(10),SozlesmeTarihi,112),5,4)<= substring(convert(varchar(10),getdate()+45,112),5,4))
or substring(convert(varchar(10),SozlesmeTarihi,112),5,4)> substring(convert(varchar(10),getdate(),112),5,4)))
or
((substring(convert(varchar(10),getdate()+45,112),1,4)<=substring(convert(varchar(10),getdate(),112),1,4))
and substring(convert(varchar(10),SozlesmeTarihi,112),5,4)
between substring(convert(varchar(10),getdate(),112),5,4) and substring(convert(varchar(10),getdate()+45,112),5,4))
)
ORDER BY MONTH(SozlesmeTarihi) DESC, DAY(SozlesmeTarihi) DESC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources