Results 1 to 2 of 2

Thread: SQL Date Query

  1. #1
    Join Date
    Jul 2002
    Location
    india
    Posts
    2

    Unanswered: SQL Date Query

    Hi all,
    i have a table like this
    create table test
    (
    appln varchar2(50), -- application name
    applndt date, -- stores application created date
    upddt date --stores application last updated date)
    /


    I have inserted records as follows

    insert into test values('app1',to_date('01-jan-1990 10:30:00','dd-mon-yyyy hh:mi:ss'),to_date('19-sep-2002 03:00:00','dd-mon-yyyy hh:mi:ss'));

    insert into test values('app2',to_date('01-jan-1995 10:30:00','dd-mon-yyyy hh:mi:ss'),to_date('19-sep-2002 03:00:00','dd-mon-yyyy hh:mi:ss'));

    Now I want a query which displays the output as follows

    app1 12 years 8 Months 18 days 4 hrs 30 min
    app2 7 years 8 months 18 days 4 hrs 30 min

    how do i write a query to get the above output

    regards
    Ravi

  2. #2
    Join Date
    Sep 2002
    Location
    Austria
    Posts
    37

    Re: SQL Date Query

    to give you a hint: have a look at:

    select appln,
    upddt-applndt as TotalDaysgone,
    MONTHS_BETWEEN (upddt,applndt) as Totalmonthsgone,
    trunc(MONTHS_BETWEEN (upddt,applndt)/12) as Yearsgone,
    MONTHS_BETWEEN (upddt,applndt) - trunc(MONTHS_BETWEEN (upddt,applndt)/12)*12 as RestOfMonths,
    trunc(MONTHS_BETWEEN (upddt,applndt) - trunc(MONTHS_BETWEEN (upddt,applndt)/12)*12) as Monthsgone
    from test;

    you could/would need the same nesting for days and hours etc.
    personally I would rather write a stored procedure (or package) to achieve the same job.
    rgds

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •