Thread: Date / interval question

Hi List;

I'm populating a time dimension. I need to get the number of days
since the start of the fiscal year and also the number of months since
the start of the fiscal year based on the current 'date' being
processed.

Example:

my current process date is 01/01/2007
start date of fiscal year is 09/01/2006

I can get the number of days since the start of the fiscal year like
this:

# select date '01/01/2007' -  date '09/01/2006' as interval;
  interval
----------
       122
(1 row)

However I'm stumped [er how to get the number of months from
09/01/2007 thru 01/01/2007

Thoughts ?

Thanks in advance...

On Thu, Apr 10, 2008 at 10:26 PM, kevin kempter
<kevin@kevinkempterllc.com> wrote:
>  However I'm stumped [er how to get the number of months from 09/01/2007
> thru 01/01/2007

select extract(month from (age(date '2007-1-1', date '2006-9-1')));

am  Thu, dem 10.04.2008, um 21:26:14 -0600 mailte kevin kempter folgendes:
> I can get the number of days since the start of the fiscal year like
> this:
>
> # select date '01/01/2007' -  date '09/01/2006' as interval;
>  interval
> ----------
>       122
> (1 row)
>
> However I'm stumped [er how to get the number of months from
> 09/01/2007 thru 01/01/2007
>
> Thoughts ?

test=*# select age(date '01/01/2007',date '09/01/2006');
  age
--------
 4 mons


For a longer interval:

test=*# select age(date '01/01/2007',date '09/05/2005');
          age
-----------------------
 1 year 3 mons 26 days
(1 row)

test=*# select extract('month' from age(date '01/01/2007',date '09/05/2005'));
 date_part
-----------
         3
(1 row)



Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net