is there a SQL command: years_between?

eurotrotter

i HAD an exam recently used this and found it on the net afterwards, but my lecturer said that it doesnt exist,
can anyone shine a light on this one for rme
thanks
ed

Tom Dunne

There is an Oracle SQL command between, though it is not only for dates.

To code years between, you would use something like this:

SELECT birthdate FROM employee_table
WHERE birthdate BETWEEN to_date('01-JAN-1970', 'DD-MON-YYYY') AND to_date('31-DEC-1975','DD-MON-YYYY')

The general format is SELECT x FROM y WHERE X BETWEEN a AND b

DublinWriter

It depends on the DBMS, but the trick is to keep it as generic as possible.

Most DBMS' let you subtract two dates and return the difference in days - divide by 365 and retain and whole number portion of the result.

bonkey

SQL is almost impossible to keep generic. You can stick to something like ANSI 92 SQL, but even then, not all popular databases will support it.

Generally, if you're being taught SQL, you're being taught it for a specific database. In this case, the answer on your exam will be judged correct or incorrect based on whether or it works on whatever DBMS you're learning.

In MSSQL, DATEDIFF is the function you'd want. Postgres, Sybase, and other also support this, IIRC.
Oracle, on the other hand, doesn't support DATEDIFF. It does, however, support MONTHS_BETWEEN.

I've never seen a years_between function, but I wouldn't rule out that some database system has it. Its also entirely possible to write it yourself in any DBMS which supports user-defined functions.

Earthhorse

Not that this makes much of a difference to the answers supplied, but are you looking for a function that allows you select rows where a date falls between two other dates or a function that calculates the difference between these dates?

seamus

Would it be possible to use the DATE_FORMAT command to get this.

E.G.
Select (DATE_FORMAT('%y', enddate) - DATE_FORMAT('%y', startdate)) as years_between from myTable

This would of course just be a replacement for DateDiff. Oracle's equivalent function for DATE_FORMAT() is TO_CHAR(), but this suggests that you wouldn't be able to perform numeric calculations on the output of TO_CHAR()

Of course, this all reinventing the wheel.

I located a user-written years_between function here:
http://forums.oracle.com/forums/thread.jspa;jsessionid=8d92200830de2d4fbc4e86e148dd8652f51f2b7c31ea.e34QbhuKaxmMai0MaNeMb3eKaxz0?messageID=1172058&#1172058

The code is below. Of course, any such user-defined functions are not ideal if you wish to make your code portable.

create or replace function years_between( d1 date, d2 date) return number is
n number;
begin
if ( d1=d2) then
return 0;
end if;
if ( d1 is null or d2 is null) then
return null;
end if;
n:=trunc( ( to_char( d2,'SYYYYMMDDHH24MISS')-to_char( d1, 'SYYYYMMDDHH24MISS'))/10000000000);
if ( to_char( d1, 'MMDD')='0229') then
if ( d1<d2) then
if ( to_char( to_date( ( to_char( d1,'SYYYY')+n)|| '060','SYYYYDDD'),'MMDD')='0229')
then
n := n + ( d2-to_date( ( to_char( d1,'SYYYY') +n)||'060'||to_char( d1,'HH24MISS'),'SYYYYDDDHH24MISS'))/366;
else
n := n + ( d2-to_date( ( to_char( d1,'SYYYY')+n) ||'060'||to_char( d1,'HH24MISS'),'SYYYYDDDHH24MISS'))/365;
end if;
else
if ( to_char( to_date( ( to_char( d1,'SYYYY')+n-1)|| '060','SYYYYDDD'),'MMDD')='0229')
then
n := n + ( d2-to_date( ( to_char( d1,'SYYYY') +n)||'060'||to_char( d1,'HH24MISS'),'SYYYYDDDHH24MISS'))/366;
else
n := n + ( d2-to_date( ( to_char( d1,'SYYYY')+n) ||'060'||to_char( d1,'HH24MISS'),'SYYYYDDDHH24MISS'))/365;
end if;
end if;
else
if ( d1<d2) then
n := n + ( d2-to_date( ( to_char( d1,'SYYYY')+n) ||to_char( d1,'MMDDHH24MISS'),'YYYYMMDDHH24MISS'))/
( to_date( ( to_char( d1,'SYYYY')+n+1)|| to_char( d1,'MMDDHH24MISS'),'YYYYMMDDHH24MISS') -
to_date( ( to_char( d1,'SYYYY')+n)|| to_char( d1,'MMDDHH24MISS'),'YYYYMMDDHH24MISS'));
else
n := n + ( d2-to_date( ( to_char( d1,'SYYYY')+n) ||to_char( d1,'MMDDHH24MISS'),'YYYYMMDDHH24MISS'))/
( to_date( ( to_char( d1,'SYYYY')+n)|| to_char( d1,'MMDDHH24MISS'),'YYYYMMDDHH24MISS') -
to_date( ( to_char( d1,'SYYYY')+n-1)|| to_char( d1,'MMDDHH24MISS'),'YYYYMMDDHH24MISS'));
end if;
end if;
return n;
end;