Search code examples
sqlsql-serverdatetimedatediff

How to consider leap year when checking for date difference in SQL

I have a situation, I save date in my database by splitting it into month,day,year while my base date is 2010-01-01.

For example,

Say my date is 2017-09-22 so this date will be saved in my database as below

basedate = 2010-01-01
comparedate = 2017-09-22
month = 92 //this is the number of month between the basedate and comparedate
day = 2821//this is the number of days between my basedate and comparedate
year = 7//this is the number of years between my basedate and comparedate

So say suppose I have to get those records which are from February irrespective of any year I am doing the below query which is working for me.

select * from XYZ where month - (year*12) = 1//1 for February.

When I am trying to get any records which is having date 12 irrespective of any month or year I am doing the below query.

select * from XYZ where day - (year*365) = 12//12 is my date.

Here I am facing the problem of leap year. My calculation goes wrong when the leap year is there in the records.

Hope I am clear with my question.

Solution

  • Why wouldn't you use 

    SELECT * FROM XYZ WHERE DATEPART(DAY, comparedate) = 12

    ?

    And also for months:

    SELECT * FROM XYZ WHERE DATEPART(MONTH, comparedate) = 2 --for februayry

    Accordingly to the coment below, I came up with another solution:

    SELECT * FROM XYZ WHERE DATEPART(DAY, DATEADD(DAY, basedate, days)) = 12

    Since basedate is base, so it should be common and can be hard-coded here. Here you simply re-determine the date it was and let SQL take care of all leap years :) Then just determine if it was 12th.