Re: Calculating difference between dates in SQL -- MIDRANGE-L

Dave--

good advice from Peter imo, would also add that ISO is ccyy-mm-dd andthe compound contents of the date function shown will convert the 8.0ccyymmdd to that format

however, not sure understanding why the example is using the DAYS( )function on each date, as this will result in a numeric for number ofdays, which will then need to be divided by 365 to yield an answerwhich does not account for leap years and such, so may be off incases where close to the anniversary date


believe what is wanted is a reliable calculation of the number of years

for this, seems it might be better to subtract the date fieldswithout wrapping them in the DAYS( ) functions, this will yield aduration in 8.0 with positions 1-4 specifying the number of years,5-6 specifying the number of months, and 7-8 the number of days


tested this with the following stepwise statements...

1) create table <testlib>/datetest (date80 numeric 8,0) not null withdefault)

2) insert into <testlib>/datetest values(19700101)
3) select * from <testlib>/datetest
   19,700,101
4) select current_date, date80 from <testlib>/datetest
   08/30/2006 19,700,101
5) select current_date, date80,

date(substr(digits(date80),1,4) || '-' || substr(digits(date80),5,2) || '-' || substr(digits(date80),7,2))

   from <testlib>/datetest
   08/30/2006 19,700,101 01/01/1970

6) select current_date - date(substr(digits(date80),1,4) || '-' ||substr(digits(date80),5,2) || '-' || substr(digits(date80),7,2))

   from <testlib>/datetest
   360729 (36 years, 07 months, 29 days)

for more detail, check out "Datetime arithmetic in SQL" in theInfoCenter


thx & hth,

--Jerome

On Aug 30, 2006, at 11:18 AM, Peter Levy wrote:

DATE requires the date to be in a date format it recognizes. Trythis expression instead to convert the date to ISO format first:
days(current date) - days(date(substr(digits(hhdob),1,4) || '-' ||substr(digits(hhdob),5,2) || '-' || substr(digits(hbdob),7,2)))
If that doesn't work take every element of the express and but themin their own column to see which one is fouling up the wholeexpression.
  ----- Original Message -----
  From: Dave Boettcher
  To: midrange-l@xxxxxxxxxxxx
  Sent: Wednesday, August 30, 2006 11:55 AM
  Subject: Calculating difference between dates in SQL


  hello,

  I have been experimenting with interactive SQL to try to formulate a
  statment for later use.
What I need to do is calculate an age for a client . The clientmaster
  contains the birth date in numeric form 8,0.
My statement, culled from various books and articles looks likethis:
  SELECT
  days(current date) - days(date(hhdob))
  "Age" ,
  hhdob, hhnaml, hhnamf,
    hhcase FROM ClientMaster

  The result always seems to look like this:

          "Age"   BIRTH YYYYMMDD            case
  ++++++++++++++    19,820,926                 4
  ++++++++++++++    19,820,924                 5
  ++++++++++++++    19,840,909                 6
  ++++++++++++++    19,820,915                 7
  ++++++++++++++    19,820,806                 8
  ++++++++++++++    19,810,108                 9
  ++++++++++++++    19,820,330                10
From reading it seems like age should be a number, why can't I seeit?
  Thanks in Advance for any help,

  Dave Boettcher

  --
This is the Midrange Systems Technical Discussion (MIDRANGE-L)mailing list
  To post a message email: MIDRANGE-L@xxxxxxxxxxxx
  To subscribe, unsubscribe, or change list options,
  visit: http://lists.midrange.com/mailman/listinfo/midrange-l
  or email: MIDRANGE-L-request@xxxxxxxxxxxx
  Before posting, please take a moment to review the archives
  at http://archive.midrange.com/midrange-l.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L)mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.