How to calculate SQL dates based on pilot's birthday as well as current date

Question

So, pilots have to get so many flight hours and flight sorties within every 6 months (semi annual) as well as every year (annual). The biggest pain is that the start and ending dates of these periods are based upon their birth month.

You can find more information about my schema here: Design decision: Table schema for partial dates in order to calculate time spans (SQL Server)

DECLARE @date Date
SET @date = '1985-04-12'

DECLARE @diffInYears INT
SET @diffInYears = DATEDIFF(yy, @date, GETDATE())

DECLARE @currentBirthDate Date
SET @currentBirthDate = (SELECT dateadd(yyyy, @diffInYears, @date))

SELECT DATEADD(dd, -DAY(DATEADD(m,1,@currentBirthDate)), DATEADD(m,7,@currentBirthDate)) semiAnnualDateEnd, 
        DATEADD(dd, -DAY(DATEADD(m,1,@currentBirthDate)), DATEADD(m,13,@currentBirthDate)) annualDateEnd

RESULTS:

semiAnnualDateEnd - annualDateEnd
   2012-10-31     -  2013-04-30

Now, this is great, these are the dates I want for this particular example.

However, when we come to November 1st, 2012, I want the semiAnnualDateEnd to become 2013-04-30.

Also, when 2013 comes around (January 1st, 2013), the annualDateEnd is going to become 2014-04-30, when I want it to stay 2013-04-30 until 2013-05-01 comes around, and then for it to become 2014-04-30 (similar situation for the semiAnnual).

I don't want to keep these dates statically associated with a particular pilot. That is, I don't want to keep a couple fields in the Pilot table that have these. However, I want to use these for displaying and calculations. For example, going to need to display each pilot's current semiAnnual and annual sorties and flight hours, as well as displaying a snapshot their "current" stats at any particular point in time.

EDIT: I'm using SQL Server 2008 Express RC

EDIT 2: I'm thinking I should change the @currentBirthDate to (SELECT DATEADD(yyyy, @diffInYears - 1, @date). Then, I need to do a case statement below (continuing experimentation)

score 1 · Answer 1 · answered Jul 23 '12 at 01:20

The rule for the semi-annual date seems to be: add five months to the month and go to the end of the month. End of the month can be a problem. So, let's change this to "add six months, got to the end of the month and subtract one day". (I am basing this logic on your example.)

The following expression does this:

select dateadd(d, -1,
               cast(cast(year(bd)+(case when month(bd)+6 > 12 then 1 else 0 end) as varchar(255))+'-'+
                    cast(case when month(bd)+7> 12 then month(bd)+6-12 else month(bd) end as varchar(255))+'-'+
                    '1' as date))
from (select cast('2011-11-01' as date) bd) t

It does the date arithmetic on the year() and month() values of the date. It then gloms them back together as a string, converts to a date, and subtracts 1 day.

I think something similar will work for your year dates as well.

did you mean go to the start of the month and subtract 1 day? Also just being picky but do you really need to cast as varchar(255)? Couldn't you do varchar(4) for the year and (2) for the month? — Greg, Jul 23 '12 at 01:28
Yes, I meant to go to the start of the month and subtract one day. It is the easiest way to find the end of the month. The size of the varchar, so long as it is big enough and <= 8000, has no effect whatsoever on query performance. The particular value is a matter of personal taste. — Gordon Linoff, Jul 23 '12 at 01:30

score 0 · Answer 2 · 2012-07-23T02:49:46.137

DECLARE @pilotID INT
SET @pilotID = 1

DECLARE @birthDate DATE  
SET @birthDate = (SELECT birthDate FROM Pilot WHERE pilotID = @pilotID)

DECLARE @diffInYears INT  
SET @diffInYears = DATEDIFF(yy, @birthDate, GETDATE())  

DECLARE @currentBirthDate DATE  
SET @currentBirthDate = (DATEADD(yyyy, @diffInYears - 1, @birthDate))  

DECLARE @firstSixMonthStart DATE
DECLARE @firstSixMonthEnd DATE  
DECLARE @secondSixMonthStart DATE
DECLARE @secondSixMonthEnd DATE  

SET @firstSixMonthStart = (DATEADD(dd,-(DAY(DATEADD(m,1,@currentBirthDate))-1),DATEADD(m,1,@currentBirthDate)))
SET @firstSixMonthEnd = (DATEADD(dd, -DAY(DATEADD(m,1,@currentBirthDate)), DATEADD(m,7,@currentBirthDate))) 
SET @secondSixMonthStart = (DATEADD(dd,-(DAY(DATEADD(m,1,@currentBirthDate))-1),DATEADD(m,7,@currentBirthDate)))
SET @secondSixMonthEnd = (DATEADD(dd, -DAY(DATEADD(m,1,@currentBirthDate)), DATEADD(m,13,@currentBirthDate)))  


DECLARE @semiAnnualStart AS DATE
DECLARE @semiAnnualEnd AS DATE 
DECLARE @annualStart AS DATE
DECLARE @annualEnd AS DATE 

SET @semiAnnualStart =  CASE   
                            WHEN GETDATE() > (DATEADD(dd, -DAY(DATEADD(m,1,@firstSixMonthEnd)), DATEADD(m,7,@firstSixMonthEnd)))  
                                THEN (DATEADD(yyyy, 1, @firstSixMonthStart))  
                            WHEN GETDATE() > @firstSixMonthEnd  
                                THEN @secondSixMonthStart      
                            ELSE @firstSixMonthStart 
                        END     

SET @semiAnnualEnd =    CASE   
                            WHEN GETDATE() > (DATEADD(dd, -DAY(DATEADD(m,1,@firstSixMonthEnd)), DATEADD(m,7,@firstSixMonthEnd)))  
                                THEN (DATEADD(yyyy, 1, @firstSixMonthEnd))  
                            WHEN GETDATE() > @firstSixMonthEnd   
                                THEN @secondSixMonthEnd    
                            ELSE @firstSixMonthEnd 
                        END     

SET @annualStart =      CASE   
                            WHEN GETDATE() > @secondSixMonthEnd THEN (DATEADD(yyyy, 1, @firstSixMonthStart))  
                            ELSE @firstSixMonthStart
                        END         

SET @annualEnd =        CASE   
                            WHEN GETDATE() > @secondSixMonthEnd THEN (DATEADD(yyyy, 1, @secondSixMonthEnd))  
                            ELSE @secondSixMonthEnd  
                        END

SELECT @semiAnnualStart semiStart, @semiAnnualEnd semiEnd, 
        @annualStart annualStart, @annualEnd annualEnd,
        @firstSixMonthStart firstStart, @firstSixMonthEnd firstEnd,
        @secondSixMonthStart secondStart, @secondSixMonthEnd secondEnd,
        COUNT(*) semiSorties, ISNULL(SUM(hours), 0) semiSortieHours
FROM PilotLog
WHERE pilotID = @pilotID
    AND topLevelPosition = 'AVO'
    AND flightDate BETWEEN @semiAnnualStart AND @semiAnnualEnd

RESULTS:

semiStart   semiEnd     annualStart annualEnd   firstStart  firstEnd    secondStart secondEnd   semiSorties semiSortieHours
2012-05-01  2012-10-31  2011-11-01  2012-10-31  2011-11-01  2012-04-30  2012-05-01  2012-10-31  1           1.7

This ended up working... Problem is, I'm going to need to do this for every pilot on the overall summary page. Additionally, I'm going to need to calculate this kind of sortie information for snapshots that she wants. She wants to go back to any particular month and see a snapshot, listing each flight as well as the sortie stats they had upon completing that flight. This SQL should work for these situations, it's just I'll have to change it up a bit here and there.

(EDIT: Why doesn't my code scroll horizontally..? I don't want it to wrap. Nevermind that's just internet explorer looks like)

How to calculate SQL dates based on pilot's birthday as well as current date

2 Answers2