Number of months between two dates

Moderator: NorbertKrupa

Post Reply
User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Number of months between two dates

Post by JimKnicely » Thu Feb 16, 2012 7:59 pm

I listened to a comedian the other day who poked fun at parents who give the age of their child in months as opposed to years even after the child is older than 12 months. Vertica must have also been listening because they included the function AGE_IN_MONTHS which returns an INTEGER value representing the difference in months between two TIMESTAMP, DATE or TIMESTAMPTZ values.

If only one date is passed into the function it will return the number of months between that date and the current system date. This is perfect for the proud parent!

Examples:

Code: Select all

dbadmin=> SELECT CURRENT_DATE() "Today's Date", AGE_IN_MONTHS(TIMESTAMP '2010-11-21') "My Baby's Age (in Months)";
 Today's Date | My Baby's Age (in Months)
--------------+---------------------------
 2012-02-16   |                        14
(1 row)

Code: Select all

dbadmin=> SELECT AGE_IN_MONTHS(TIMESTAMP '2013-01-01', TIMESTAMP '2012-01-01') "One Year";
 One Year
----------
       12
(1 row)
If the first date is earlier than the second date, the result is negative:

Code: Select all

dbadmin=> SELECT AGE_IN_MONTHS(TIMESTAMP '2010-12-12', TIMESTAMP '2012-01-01') "Negative Months";
 Negative Months
-----------------
             -13
(1 row)
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Post Reply

Return to “Vertica SQL”