Link to home
Start Free TrialLog in
Avatar of gvamsimba
gvamsimbaFlag for United Kingdom of Great Britain and Northern Ireland

asked on

problem where datediff is zero when comparing january and december dates in the same year.

in below example by query is rightly returning date as '2012-01-10 00:00:00.000' because
that is the next anniversary date of my effectivedate.

But when i change the value of jobrundate to '20121220', my query still returns '2012-01-10 00:00:00.000' but it need to return the next anniversary date which is '2013-01-10 00:00:00.000'

CAn any one please update my date statement below to reflect my requirement ?

 Declare @jobrunDate DateTime
  declare @effectivedate datetime
Set @jobrunDate = '20121220'
set @effectivedate = '20110110'

select  DATEADD(YYYY,DATEDIFF(YYYY,@effectivedate,@jobrunDate),@effectivedate) -- MY OLD query which does not pick up the next year

select  DATEADD(YYYY,CASE WHEN DATEDIFF(YYYY,@effectivedate,@jobrunDate) = 0 THEN 1 ELSE DATEDIFF(YYYY,@effectivedate,@jobrunDate) END,@effectivedate) -- my modifed query which works only for 1st year

Many THanks
Avatar of knightEknight
Flag of United States of America image

Something to try:

  select  DATEADD( YYYY, DATEDIFF(YYYY,@effectivedate,@jobrunDate)+1, @effectivedate )

If this isn't what you need, please provide expected results for various values of @jobrunDate and @effectivedate.
Avatar of gvamsimba


Hi if my @jobrunDate is 20111219 and my @effectivedate is 20110110 my query needs to return the date which is 12 months after my @effectivedate which in this case should be 20120110.

When my job runs next after 1year where @jobrundate is 20121219 and my effectivedate being the same as above, the query should return the NEXT anniversary date of my effectivedate which in this case should be 20130110.

Does my suggestion above not work as you need it to?
no your query will not work because it does not give the desired date when i run like below -
the correct result should be '2012-11-29 00:00:00.000'  bcos it is the anniversary date of the effective date below... but ur query is adding up one more extra year and giving '2013-11-29 00:00:00.000' which is wrong...

 Declare @jobrunDate DateTime
  declare @effectivedate datetime
Set @jobrunDate = GETDATE()
set @effectivedate = '20111129'
Avatar of gvamsimba
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
my condition will give me exactly bwhat I am looking for....