SQLTeam.com | Weblogs | Forums

How to auto change the date (Only Year)


#1
Select a.EEIDNO, a.Salary, e.Empdte, DATEDIFF(Day, e.Empdte, a.CUTOFFDTE),
Case 
When (DATEDIFF(Day, e.Empdte, a.CUTOFFDTE) + 1) / 365.0 >= 1 Then
a.Salary
When (DATEDIFF(Day, e.Empdte, a.CUTOFFDTE) + 1) / 365.0 < 1 And e.Empdte <= '2017/06/30' Then 
a.Salary * ((DATEDIFF(Day, e.Empdte, a.CUTOFFDTE) + 1) / (DATEDIFF(Day, '2017/01/01', 
a.CUTOFFDTE) + 1))
Else
0
End As Discretionary_Bonus
From (SELECT '2017/12/31' AS CUTOFFDTE, a1.* FROM APPRAISAL a1) a, EEMASTER e
WHERE a.EFFECTDTE <= (SELECT MAX(z.EFFECTDTE) 
FROM  APPRAISAL z WHERE a.EEIDNO = z.EEIDNO AND z.EFFECTDTE <= a.CUTOFFDTE)
AND e.EEIDNO = a.EEIDNO AND (e.STATUS = 'A' OR (e.STATUS = 'T' AND e.LASTWDTE > 
a.CUTOFFDTE))
order by e.EEIDNO

For example : When I change the year ('2017/12/31') to ('2018/12/31') the other two part can auto change the year just like ('2018/06/30' ) and ('2018/01/01')


#2

If you are asking about changing the query, use REPLACE ALL in the query window. But I suspect you are asking something else, and I don't know what it is.

Looking at your query, the division by 365 etc. make it seem like the logic may not be quite robust e.g., will it work correctly in leap years? There are well-established patterns in T-SQL for working with dates. So if you can describe the logic you are trying to implement, someone on the forum should be able to help you.


#3

Don't use <= on the ending date, use <. That allows you to always use the first of the next month, thus, you don't have to worry about how many days are in any month.

And e.Empdte < '2017/07/01'