Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4

    Unanswered: calculate number of started months

    I should calculate the number of months between two dates. If a month is started for 1 day or 20, it should be counted as one month.
    eg:
    start date 01 jan 2006

    with end date 30 jan 2006 : 1 month
    with end date 01 feb 2006 : 1 month
    with end date 02 feb 2006 : 2 months
    with end date 28 feb 2006 : 2 months
    with end date 01 mar 2006 : 2 months
    with end date 02 mar 2006 : 3 months

    I've been thinking of using:
    MONTH(DA_LAST) - MONTH(DA_FIRST) +
    CASE WHEN DAY(DA_LAST) > DAY(DA_FIRST) THEN 1 ELSE 0 END

    but it fails with eg
    start date 01 dec 2005 and end date 30 jan 2006
    start date 28 feb 2006 and end date 30 mar 2006

    I will change job (datawarehouse and datamining) within a few days and I would like to finish my current project before leaving - I have great end users -. Can you help me ?
    Last edited by Wim; 04-26-06 at 21:08.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  2. #2
    Join Date
    Apr 2006
    Posts
    1

    Cool Try this

    declare @First datetime
    declare @Last datetime
    set @First = '2006-01-01'
    set @Last = '2006-03-02'

    select
    case
    when month(@last) = month(@first)
    then
    1
    else
    month(@last) - month(@first) +
    (case when day(@last) = 1 then 0 else 1 end)
    end

  3. #3
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    This suffers from the same problem as my first attempt, it doesn't handle
    da_first = '2005-12-30'
    da_last = '2006-01-02'

    I came up with this
    Code:
    SELECT (
    	(MONTH(da_end) - MONTH(da_start)) +
    	(12 * (YEAR(da_end) - YEAR(da_start))) +
    	(CASE WHEN DAY(da_end) > DAY(da_start) THEN 1 else 0 END)
    	) as nbr_months
    FROM (SELECT cast('2006-02-28' as date) as da_start,
    		cast('2006-03-30' as date) as da_end
    	FROM "SYSIBM"."SYSDUMMY1"
    	) as T
    ;
    it handles da_start = '2005-12-30' and da_end = '2006-01-02' correct
    but it can't handle the current example: it should return 1, but it returns 2
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I think this will work:
    Code:
    select (
       int((date('2006-03-02') - date('2006-01-01'))/100) +  
       sign(mod(int(date('2006-03-02') - date('2006-01-01')),100))
    ) from sysibm.sysdummy1
    If your dates can span more than a year you'll need to account for that. Subtraction of two dates yields a decimal value with the following format: "yymmdd".

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •