getting leap year and non leap year in report

Question

getting leap year and non leap year in report

Viewing 7 posts - 1 through 8 (of 8 total)

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

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 1

Have you considered creating a calendar table and using this to identify the leap years?

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

naeemkhan72 Old Hand Points: 311 More actions · Answer 2

I dont want to create a table to hold and highlight the leap years, I'm assuming there must be a way to do it without the use of an extra table.

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 3

naeemkhan72 (7/11/2012)
I dont want to create a table to hold and highlight the leap years, I'm assuming there must be a way to do it without the use of an extra table.

There is no system function in SQL Server that will highlight the leap year

You can create some user defined function for it or else you can use one of the methods you suggested

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 4

I dont want to create a table to hold and highlight the leap years, I'm assuming there must be a way to do it without the use of an extra table

Yes there are but not without scripting it yourself which come with an overhead at runtime.

The quickest and most efficient way is a calendar table (which you have disregarded) therefore your option remaining is to incorporate a UDF or logic within your SQL script.

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

Andy Hyslop SSCrazy Eights Points: 9198 More actions · Answer 5

This may be of help..

http://www.blackwasp.co.uk/SQLIsLeapYear.aspx

==========================================================================================================================
A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

naeemkhan72 Old Hand Points: 311 More actions · Answer 6

naeemkhan72

Old Hand

Points: 311

July 11, 2012 at 8:09 am

#1511564

Thank you

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

I am confused slightly by your initial post. Your procedure takes two dates as input parameters, a start date and an end date. As you did not provide any DDL (CREATE TABLE statements) or sample data (as a series of INSERT INTO statements) for any tables, it is difficult to know exactly what you are trying to accomplish and what the actual problem is other than you can't seem to get last years end date for February if it happened to be a leap year.

Here is what I noticed looking at your code, you are using closed endded comparisions for your date ranges. While this may be fine when you know for certain that there is no time portion in the datetime data, the safest way to query a date range is to use a closed ended comparision on the lower end (for example MyDateCol >= '20110301) and an open ended comparision on the upper end of the range (for example MyDateCol < '20120301'). The range given in the examples I gave will ensure that you get all records for March 2011 through February 2012.

Any questions?