A proper date conversion function?

[fernando.olmos at hpa.com.au]

Thanks Murray..
 
That is infinetely better than anything I've seen!    :-0
 
No... that's great really... thank you. I never thought of using
matchpattern().
 
cheers
 
 
ps.. say hi to Rod for me.
 

Kind regards, 

--- 
Fernando Olmos 
Senior Analyst Programmer 
HPA 
Direct: 03 9217 5411 
Fax: 03 9217 5166 
 <http://www.hpa.com.au/> www.hpa.com.au 

-----Original Message-----
From: murray.scholz at abri.une.edu.au
[mailto:murray.scholz at abri.une.edu.au] 
Sent: Friday, 21 September 2007 11:49 AM
To: Fernando Olmos
Cc: powerh-l at lists.sowder.com
Subject: Re: A proper date conversion function?


Hi Fernando,

I agree , a dateconv() function would make life a load easier.

We frequently deal with incoming data using the something similar to
following...

Once you 'uncover' new possibilities that are coming in, it's just a
matter of adding a new 'style' to the 1st define, and then picking off
the components that are required.  Tedious & somewhat pedestrian I know,
but it's easy code to follow...
You could include fancy checks for leap years etc.

define fld-2 char*50 = "24102001"

define fld-2-style int*4 = 1 if matchpattern(fld-2,"##(/|-)##(/|-)####
*") &
        else 2 if matchpattern(fld-2,"##(/|-)#(/|-)#### *") &
        else 3 if matchpattern(fld-2,"#(/|-)##(/|-)#### *") &
        else 4 if matchpattern(fld-2,"#(/|-)#(/|-)#### *") &
        else 9 if matchpattern(fld-2,"######## *") &
        else 0
define fld-2-dd int*9 = 0 if fld-2-style = 0 &
        else nconvert(fld-2[1:1]) if fld-2-style = 3 or fld-2-style = 4
&
        else nconvert(fld-2[1:2])
define fld-2-mm int*9 = 0 if fld-2-style = 0 &
        else nconvert(fld-2[4:2]) if fld-2-style = 1 &
        else nconvert(fld-2[4:1]) if fld-2-style = 2 &
        else nconvert(fld-2[3:2]) if fld-2-style = 3 or fld-2-style = 9
&
        else nconvert(fld-2[3:1])
define fld-2-yyyy int*9 = 0 if fld-2-style = 0 &
        else nconvert(fld-2[7:4]) if fld-2-style = 1 &
        else nconvert(fld-2[6:4]) if fld-2-style = 2 or fld-2-style = 3
&
        else nconvert(fld-2[5:4])
define fld-2-n int*9 = 0 if fld-2 = " " &
        else 0 if fld-2-dd = 0 or fld-2-mm = 0 or fld-2-yyyy = 0 &
        else 0 if fld-2-mm > 12 or fld-2-dd > 31 &
        else 0 if fld-2-dd > 30 and (fld-2-mm = 2 or fld-2-mm = 4 or
fld-2-mm = 6 or fld-2-mm = 9 or fld-2-mm = 11) &
        else 0 if fld-2-dd > 28 and fld-2-mm = 2 &
        else (10000 * fld-2-yyyy) + (100 * fld-2-mm) + fld-2-dd

define fld-2-date date = fld-2-n

Regards
Murray Scholz

Agricultural Business Research Institute

University of New England

Armidale NSW 2351 Australia

Email  murray.scholz at abri.une.edu.au
<mailto:murray.scholz at abri.une.edu.au> 





fernando.olmos at hpa.com.au <mailto:fernando.olmos at hpa.com.au>  wrote: 

Hi everyone. 
This is interesting... there must be a good date conversion function,
using defines, out there that is better than mine... 

This only works with dd/mm/yyyy, where dd and mm can be either single or
two values. What if the field t_field_03 (char*50 by the way) has an
invalid date like "1212/344/54874" or  a valid date of ddmmyyyy?

>From the client that sends us the files, they can send us anything,
including any valid date like ddmmyyyy, dd-mm-yyyy, dmy, dmmyyyy,
ddmyyyy, dmyyyy, etc... so we have to cater for all possible dates. It's
a shame COGNOS didn't think of writing a dateconv() function that
actually converts any date to a valid date data type. If there is one,
please let me know as I am pulling out my hair with all these defines!
hehehe

;Get the date required, but making sure that day and month 
;is properly adjusted if they are single digits. Format of the 
;date has to come in with 'slash' characters in the format: dd/mm/yyyy 

define t_slash1 num*2 = index(t_field_03,"/") 
define t_mmyyyy char*7 = t_field_03[(t_slash1 + 1):7] 
define t_slash2 num*2 = index(t_mmyyyy,"/") 
define t_date_required_d char*2 = t_field_03[1:(t_slash1 - 1)] & 
       if t_slash1 > 2 and t_rec_type = 1 & 
     else "0" + t_field_03[1:(t_slash1 - 1)] & 
       if t_rec_type = 1 
define t_date_required_m char*2 = t_mmyyyy[1:(t_slash2 - 1)] & 
       if t_slash2 > 2 and t_rec_type = 1 & 
     else "0" + t_mmyyyy[1:(t_slash2 - 1)] & 
       if t_rec_type = 1 
define t_date_required_y char*4 = t_mmyyyy[(index(t_mmyyyy,"/") + 1):4]
& 
       if t_rec_type = 1 
define t_date_required_c char*8 = pack(trunc(t_date_required_y) + & 
                                       trunc(t_date_required_m) + & 
                                       trunc(t_date_required_d)) & 
                              if t_num_error <> "C"       & 
                             and t_rec_type = 1        & 
                     else "0" 
define t_date_required date = nconvert(t_date_required_c) 





**********************************************************************
IMPORTANT
The contents of this e-mail and its attachments are confidential and intended
solely for the use of the individual or entity to whom they are addressed.  If
you received this e-mail in error, please notify the HPA Postmaster, postmaster at hpa.com.au,
then delete  the e-mail.
This footnote also confirms that this e-mail message has been swept for the
presence of computer viruses by Ironport. Before opening or using any
attachments, check them for viruses and defects.
Our liability is limited to resupplying any affected attachments.
HPA collects personal information to provide and market our services. For more
information about use, disclosure and access see our Privacy Policy at
www.hpa.com.au
**********************************************************************
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.sowder.com/pipermail/powerh-l/attachments/20070921/25db70d3/attachment.html