A proper date conversion function?
Jeff Hoffman
wonicon at optusnet.com.au
Fri Sep 21 10:48:28 CDT 2007
Hi
I know this might sound slack and it might not
work in QTP, but could you not just set the field
to a date field, if the result is 0 then the date was invalid?
Jeff
At 11:59 AM 21/09/2007, you wrote:
>content-class: urn:content-classes:message
>Content-Type: multipart/alternative;
> boundary="----_=_NextPart_001_01C7FBF3.1C336DEE"
>
>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 <mailto:murray.scholz at abri.une.edu.au>murray.scholz at abri.une.edu.au
>
>
>
>
><mailto:fernando.olmos at hpa.com.au>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
>**********************************************************************
>--
>= = = = = = = = = = = = = = = = = = = = = = = = = = = =
>Mailing list: powerh-l at lists.sowder.com
>Subscribe: "subscribe" in message body
>to powerh-l-request at lists.sowder.com
>Unsubscribe: "unsubscribe
><password>" in message body to powerh-l-request at lists.sowder.com
>http://lists.sowder.com/mailman/listinfo/powerh-l
>This list is closed, thus to post to the list you must be a subscriber.
>Add 'site:lists.sowder.com powerh-l' to your
>search terms to search the list archive at Google.
More information about the powerh-l
mailing list