Re: Calculating difference between dates in SQL

Thanks, if I read right,  to Mark, Peter and Jerome,

I took the days function out, used the timestamp idea and it worked.

Statement now looks like this:
SELECT     (current date) -     (date( timestamp( digits(hhdob) || 
'000000' ))) "Age" , hhdob, hhnaml, hhnamf, hhcase FROM ClientMasteer  


With these results:

  "Age"   BIRTH YYYYMMDD 
231,104     19,820,926   
231,106     19,820,924   
211,121     19,840,909   
231,115     19,820,915   
240,024     19,820,806   
250,722     19,810,108   
240,500     19,820,330   
200,725     19,860,105   
240,007     19,820,823   

More like what I expected.

Thanks for all the help.

Dave B

jromeh@xxxxxxx 08/30/2006 1:22:47 PM >>>

Dave--

good advice from Peter imo, would also add that ISO is ccyy-mm-dd and 

the compound contents of the date function shown will convert the 8.0 

ccyymmdd 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 of 

days, which will then need to be divided by 365 to yield an answer  
which does not account for leap years and such, so may be off in  
cases 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 fields  
without wrapping them in the DAYS( ) functions, this will yield a  
duration 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 with 

default)
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 the  
InfoCenter

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. Try  
this 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 them 

in their own column to see which one is fouling up the whole  
expression.
  ----- 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 client  
master
  contains the birth date in numeric form 8,0.

  My statement, culled from various books and articles looks like  
this:

  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 see 

it?

  Thanks in Advance for any help,

  Dave Boettcher