Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2009
    Posts
    62

    Unanswered: Get last day of month for each month between 2 dates

    Hi,

    I'm trying to write some code that will get me the last day of each month for the months that fall between 2 dates.

    So if i have a table with a record with a begin date of 01-01-2012 and an end date of 09-14-2012, i would want a result set of the following 9 records:

    01-31-2012
    02-29-2012
    03-31-2012
    04-30-2012
    05-31-2012
    06-30-2012
    07-31-2012
    08-31-2012
    09-30-2012

    So i basically get the last day of each month between the dates listed, including the months that the dates are in themselves (inclusive list).

    I have a date dimension table with 1 row for every day from 1990 to 2025. There is also a field with the Last day of Month for a given date. My issue is that I'm not sure how to write the T-SQL to get what I need.

    any help is appreciated.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    if you are using SQL 2012, you can use EOMONTH (Transact-SQL)

    If not, you could use:
    Code:
    SELECT DateAdd(m, number, '1990-01-31')
       FROM master.dbo.spt_values
       WHERE  'P' = type
          AND DateAdd(m, number, '1990-01-31') < '2026-01-01'
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Sep 2009
    Posts
    62
    Thanks Pat,

    but how would i incoporate this code into a query if I had a table like this:

    Code:
    ID     Effective       Expiration
    123   01-01-2012   09-30-2012
    I want the result to be:

    Code:
    ID    Month End     Effective       Expiration
    123  01-31-2012   01-01-2012   09-30-2012
    123  02-29-2012   01-01-2012   09-30-2012
    123  03-31-2012   01-01-2012   09-30-2012
    123  04-30-2012   01-01-2012   09-30-2012
    123  05-31-2012   01-01-2012   09-30-2012
    123  06-30-2012   01-01-2012   09-30-2012
    123  07-31-2012   01-01-2012   09-30-2012
    123  08-31-2012   01-01-2012   09-30-2012
    123  09-30-2012   01-01-2012   09-30-2012

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Without knowing the details of your date table, I can't use it even though it might give a simpler answer. You could use:
    Code:
    CREATE TABLE #scabral7 (
       ID           BIGINT
    ,  Effective    DATETIME
    ,  Expiration   DATETIME
       )
    
    INSERT INTO #scabral7 (
       ID, Effective, Expiration)
       SELECT 123, '2012-01-01', '2012-09-30'
    
    SELECT ID, e, Effective, Expiration
       FROM #scabral7 AS a
       INNER JOIN (SELECT
          DateAdd(m, number, '1990-01-01') AS b
    ,     DateAdd(m, number, '1990-01-31') AS e
          FROM master.dbo.spt_values
    	  WHERE  'P' = type) AS b
          ON (b.b BETWEEN DateAdd(m, DateDiff(m, 0, a.Effective), 0) AND a.Expiration)
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    Sep 2009
    Posts
    62
    Thanks Pat,

    this looks like it will work.

Posting Permissions

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