How many months are in a range of dates?

How many months are in a range of dates?

Post by Claudio Long » Thu, 20 Jul 1995 04:00:00



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?

Post by Rick Rut » Sat, 22 Jul 1995 04:00:00



>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?

Post by Marc de Brouw » Wed, 26 Jul 1995 04:00:00


: 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?

Post by Slavko Stemberg » Fri, 28 Jul 1995 04:00:00



>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.

 
 
 

1. SQL for all months in range, data or not

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

2. Exus Foot Craz Activity Pad

3. How am supposed to know which init file I am using pfile or spfile?

4. CLOSEOUTS on Motherboards/CD-ROM's.....Offer ends May 5th !!

5. sql to find a numeric range inside another range?

6. Credit Card Payment vs Transfer - Money 2002

7. Tricky date range calculations - another look.

8. Isdn call history

9. Need help with date range inclusion.

10. Time in varchar and date ranges - a SQL poser

11. Dates where day/month is unknown

12. OPO/PO7: Date and MONTH()