How to change the Year of a 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

General SQL Server Forums

New to SQL Server Programming

How to change the Year of a date

Author

Topic

DaveBF
Yak Posting Veteran

89 Posts

Posted - 2011-03-09 : 12:57:21

I have a column called DueDate and I want to use in a Where clause, and see if the day and month portion of that date is within 90 days. So for example, if DueDate contains 4/15/1998 and today is 3/8/2011 then it would return true but a DueDate of 8/15/1998 would return false.

How can I do that?
Thanks.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-03-09 : 13:21:12

e.g.,
select name,
Case when Create_date < dateadd(dd,-90,getdate()) Then 'True'
Else 'False' end from sys.objects

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-03-09 : 13:23:05

Even '8/15/1998' is more than 90 days.

Select dateadd(dd,90,'4/15/1998')

DaveBF
Yak Posting Veteran

89 Posts

Posted - 2011-03-09 : 13:30:19

This isn't what I wanted. Maybe I didn't explain it well.

For a given date, I want to ignore the year portion, and see if it's more than 90 days from now. So if I ran it today, 4/1/1998 would be true but 9/1/1998 would be false.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2011-03-09 : 13:52:10

One way:

Declare @test datetime
set @test ='4/1/1998'

Select Case when Datediff(dd,90 ,dateadd(yy,datediff(yy,@test,getdate()),@test )) < GetDate() Then 'True' else 'False' end

Declare @test1 datetime
set @test1 ='9/1/1998'

Select Case when Datediff(dd,90 ,dateadd(yy,datediff(yy,@test1,getdate()),@test1 )) < GetDate() Then 'True' else 'False' end

I feel it can still be simplified

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-09 : 14:06:04

Interesting problem Dave and PK_Bohra :--) I have an even more complicated solution. There has to be a simpler way.

where
   (abs(datepart(dayofyear,DueDate )-datepart(dayofyear,@date)) <= 90)
   or
   ((365-datepart(dayofyear,DueDate)+datepart(dayofyear,@date))
      +case when year(DueDate)%4=0 then 1 else 0 end <= 90)
   or
   ((365-datepart(dayofyear,@date)+datepart(dayofyear,DueDate))
      +case when year(@date)%4=0 then 1 else 0 end <= 90 )

DaveBF
Yak Posting Veteran

89 Posts

Posted - 2011-03-09 : 14:06:57

quote:
Originally posted by pk_bohra

One way:

Declare @test datetime
set @test ='4/1/1998'

Select Case when Datediff(dd,90 ,dateadd(yy,datediff(yy,@test,getdate()),@test )) < GetDate() Then 'True' else 'False' end

Declare @test1 datetime
set @test1 ='9/1/1998'

Select Case when Datediff(dd,90 ,dateadd(yy,datediff(yy,@test1,getdate()),@test1 )) < GetDate() Then 'True' else 'False' end

I feel it can still be simplified

This doesn't work.
select Datediff(dd,90 ,dateadd(yy,datediff(yy,'4/1/1998',getdate()),'4/1/1998' ))

returns 40542.

DaveBF
Yak Posting Veteran

89 Posts

Posted - 2011-03-09 : 14:14:10

quote:

Originally posted by sunitabeck

Interesting problem Dave and PK_Bohra :--) I have an even more complicated solution. There has to be a simpler way.

where
   (abs(datepart(dayofyear,DueDate )-datepart(dayofyear,@date)) <= 90)
   or
   ((365-datepart(dayofyear,DueDate)+datepart(dayofyear,@date))
      +case when year(DueDate)%4=0 then 1 else 0 end <= 90)
   or
   ((365-datepart(dayofyear,@date)+datepart(dayofyear,DueDate))
      +case when year(@date)%4=0 then 1 else 0 end <= 90 )

abs(datepart(dayofyear,DueDate )-datepart(dayofyear,@date)) <= 90)

seems to be able to give me what I want.
Thank you!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-09 : 14:18:59

quote:
Originally posted by DaveBF

abs(datepart(dayofyear,DueDate )-datepart(dayofyear,@date)) <= 90)

seems to be able to give me what I want.
Thank you!

I think you need the other two parts too. For example, try with with DueDate = 12/31/1998 and @date = 1/1/1999. Don't you want that to be filtered in as being within 90 days.

DaveBF
Yak Posting Veteran

89 Posts

Posted - 2011-03-09 : 14:35:54

Yes, I do need it all. thanks.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-09 : 15:42:49

DECLARE @date datetime
SET @date = '19980415'

select
dateadd(day,datepart(dy,@date)-1,dateadd( year, datediff(year,0,@date )+year(getdate())-year(@date),0
))
, dateadd( day, datediff(day,0,getdate() ),90
)

Jim

Everyday I learn something that somebody else already knew

DaveBF
Yak Posting Veteran

89 Posts

Posted - 2011-03-10 : 07:24:23

quote:
Originally posted by jimf

DECLARE @date datetime
SET @date = '19980415'

select
dateadd(day,datepart(dy,@date)-1,dateadd( year, datediff(year,0,@date )+year(getdate())-year(@date),0
))
, dateadd( day, datediff(day,0,getdate() ),90
)

Jim

Everyday I learn something that somebody else already knew

thank you for spending time on this. I'm not sure it will work in all cases. When today's date is 12/15/2011, and @date is 1/15/1998, I don't think it will work.
This seems to work, although I'm still testing:
select
dateadd(day,datepart(dy,@date)-1,dateadd( year, datediff(year,0,@date )+
year(dateadd(dy,90,'20111215'))-year(@date),0
))
, dateadd( day, datediff(day,0,'20111215' ),90

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources