## How many months are in a range of dates?

### How many months are in a range of dates?

Using a column (hire_date) and sysdate, how can I select the
number of months a person has worked.

### How many months are in a range of dates?

>Using a column (hire_date) and sysdate, how can I select the
>number of months a person has worked.

Use the MONTHS_BETWEEN(date1,date2) function, documented on page 2-57
of the PL/SQL manual (version 2.0).  This returns a signed decimal number
of months, and fractions of months between two dates.  The last date in
each month is treated special, so comparing to end-of-month dates always
returns an even number of months (with no fractional part).

-- Rick
--

(Rick Rutt is a system architect living and working in Midland, Michigan.)

### How many months are in a range of dates?

: Using a column (hire_date) and sysdate, how can I select the
: number of months a person has worked.

R
Claudio,

this is relatively straightforward: there exists a SQL function called
months_between which determines the number of months between two dates,
e.g. "select months_between(a.hire_date,sysdate) from my_table a"
Hope this is what you are looking for.

Marc.

-- These are my own opinions, not Oracle's

### How many months are in a range of dates?

>Using a column (hire_date) and sysdate, how can I select the
>number of months a person has worked.

Try
select months_between(sysdate, hire_date)
from emp
where ...

This will calculate the months a person has worked. You may wish to
round or truncate this value depending on your needs.

Hi,

Need to get column headers in SQL for months regardless of whether
data exists.

DB is public school data system for large city - 55 schools.  Columns
are Sep through Jun, taken from transaction date (using Oracle
function) for a student.  Rows are various categories. Body of table
is student count for that school and category by month but there is
not necessarily a student count for every category for every school
every month.  However, school board wants all tables to line up
visually vertically, even if no data.  Result table being used by
another tool (Business Objects) for display.

Thought of using in line table, e.g.
SELECT
data1,
data2,
etc.
FROM
table1,
table2,
(Select 'Jan','Feb','Mar', etc from SYS.DUAL) etc.
WHERE
table1.school_year = 'YY',  -- where YY is school year,
-- e.g. Sep 2000 - Jun 2001, YY = '00'
???

What do I need here?  I understand outer joins but how do I outer join
an in line table?

Note that the selection for school year is NOT based on the
transaction date.  school_year is column in source table.  All that is
necessary is that the months taken from transaction date can be
matched to the in line table, or whatever is used.

Any ideas?  I am not SQL guru, so this may seem simple to some.

Thanks.

Tom Eischeid