help with leap year

Question

help with leap year

web foo

SSC Rookie

Points: 31
More actions
October 19, 2008 at 11:39 pm

#194909

I have this view viewALL that gets information based on 4 weeks from the days date.
SELECT TOP (100) PERCENT AllocatedHrs AS TotalHours, AllocatedHrs - AllocatedHrs AS PastDemand, DATEPART(wk, ActualDueDate) AS TheWeekID,
MachineID
FROM dbo.tblMachineRunTime
WHERE (TaskStatusID = 2) AND (ActualDueDate BETWEEN GETDATE() - 42 AND GETDATE() - 14)
ORDER BY TheWeekID
How can i format t he code to detect leap year.
Thanks very much

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Mike John SSCertifiable Points: 7216 More actions · Answer 1

Not sure what you bean by detect the leap year - your code seems to be using a fixed 28 day window rather than anything to do with months (specifically february) so I am not sure what leap years have to do with it.

Anyway the formula for a leap year can be built as a case expression:

select case when ( year(getdate()) % 4 = 0 AND YEAR( getdate()) % 100 != 0) or year(getdate()) % 400 = 0 hen 1 else 0 end as leap

which may help.

I woul d also start avoiding the trick of select top 100% in a view, it may appear to work but it is well documented that this should really be regarded as a loophole at 200 and best avoided.

Mike John

Wilfred van Dijk SSCrazy Eights Points: 9003 More actions · Answer 2

Found this trick for determing a leap year (thought it came from this site)

if object_id('udf_IsLeapYear') is not null

drop function udf_IsLeapYear

GO

create function dbo.udf_IsLeapYear (@year int)

returns bit

as

begin

return(select case datepart(mm, dateadd(dd, 1, cast((cast(@year as varchar(4)) + '0228') as datetime)))

when 2 then 1

else 0

end)

end

go

Wilfred
The best things in life are the simple things

RBarryYoung SSC Guru Points: 143329 More actions · Answer 3

Nice, I like that one.

Is that the original name?

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Wilfred van Dijk SSCrazy Eights Points: 9003 More actions · Answer 4

No. Found the original location: http://www.mssqltips.com/tip.asp?tip=1527

Wilfred
The best things in life are the simple things

RBarryYoung SSC Guru Points: 143329 More actions · Answer 5

Thanks!

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 6

Heh... you can do it a bit easier than that... not sure it's even worth putting in a function...

[font="Courier New"]DECLARE @Year INT

SET @Year = 2007

SELECT ISDATE(STR(@Year)+'0229')[/font]

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

Nice!

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

AnzioBake SSCarpal Tunnel Points: 4134 More actions · Answer 8

If you had a Date you could also use

Declare @Date int

Set @Date = getDate()

Select Case Datepart( year, @Date ) % 4

when 0 Then 1

Else 0

End

Wilfred van Dijk SSCrazy Eights Points: 9003 More actions · Answer 9

No, that's incorrect.

See the exact formulas for determing a leap year on http://en.wikipedia.org/wiki/Leap_year

Wilfred
The best things in life are the simple things

AnzioBake SSCarpal Tunnel Points: 4134 More actions · Answer 10

Interesting article. even there it seems one can calculate it with different functions ...