How to exclude the Leap day - SQL Server Forums

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)

How to exclude the Leap day

Author

Topic

khufiamalik
Posting Yak Master

120 Posts

Posted - 2010-02-20 : 02:23:03

Hello all,
I have 2 Date variables which are @Employee_Joining_Date and @Employee_Resign_Date.
I have to calculate the total Number of Job Days BUT if there is any Leap Year(s) between theses 2 dates then ignore the LEAP DAY.

any Idea ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-20 : 02:33:41

[code]
DECLARE @Employee_Joining_Date datetime, @Employee_Resign_Date datetime
SELECT @Employee_Joining_Date='20040831',@Employee_Resign_Date='20091223'

SELECT COUNT(CASE WHEN DAY([Date]) = 29 AND MONTH([Date])= 2 THEN NULL ELSE 1 END) AS NonLeapCountJobDays
FROM dbo.CalendarTable(@Employee_Joining_Date,@Employee_Resign_Date,1)

calendartable function can be found at

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

output
------------------
NonLeapCountJobDays
1386

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/