<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
Hi Fernando,<br>
<br>
I agree , a dateconv() function would make life a load easier.<br>
<br>
We frequently deal with incoming data using the something similar to
following...<br>
<br>
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...<br>
You could include fancy checks for leap years etc.<br>
<br>
define fld-2 char*50 = "24102001"<br>
<br>
define fld-2-style int*4 = 1 if matchpattern(fld-2,"##(/|-)##(/|-)####
*") &<br>
else 2 if matchpattern(fld-2,"##(/|-)#(/|-)#### *") &<br>
else 3 if matchpattern(fld-2,"#(/|-)##(/|-)#### *") &<br>
else 4 if matchpattern(fld-2,"#(/|-)#(/|-)#### *") &<br>
else 9 if matchpattern(fld-2,"######## *") &<br>
else 0<br>
define fld-2-dd int*9 = 0 if fld-2-style = 0 &<br>
else nconvert(fld-2[1:1]) if fld-2-style = 3 or fld-2-style = 4
&<br>
else nconvert(fld-2[1:2])<br>
define fld-2-mm int*9 = 0 if fld-2-style = 0 &<br>
else nconvert(fld-2[4:2]) if fld-2-style = 1 &<br>
else nconvert(fld-2[4:1]) if fld-2-style = 2 &<br>
else nconvert(fld-2[3:2]) if fld-2-style = 3 or fld-2-style = 9
&<br>
else nconvert(fld-2[3:1])<br>
define fld-2-yyyy int*9 = 0 if fld-2-style = 0 &<br>
else nconvert(fld-2[7:4]) if fld-2-style = 1 &<br>
else nconvert(fld-2[6:4]) if fld-2-style = 2 or fld-2-style = 3
&<br>
else nconvert(fld-2[5:4])<br>
define fld-2-n int*9 = 0 if fld-2 = " " &<br>
else 0 if fld-2-dd = 0 or fld-2-mm = 0 or fld-2-yyyy = 0 &<br>
else 0 if fld-2-mm > 12 or fld-2-dd > 31 &<br>
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) &<br>
else 0 if fld-2-dd > 28 and fld-2-mm = 2 &<br>
else (10000 * fld-2-yyyy) + (100 * fld-2-mm) + fld-2-dd<br>
<br>
define fld-2-date date = fld-2-n<br>
<br>
Regards<br>
Murray Scholz<br>
<pre class="moz-signature" cols="72">Agricultural Business Research Institute
University of New England
Armidale NSW 2351 Australia
Email <a class="moz-txt-link-abbreviated" href="mailto:murray.scholz@abri.une.edu.au">murray.scholz@abri.une.edu.au</a>
</pre>
<br>
<br>
<br>
<a class="moz-txt-link-abbreviated" href="mailto:fernando.olmos@hpa.com.au">fernando.olmos@hpa.com.au</a> wrote:
<blockquote cite="midC5B2133CD2641C4EA593D2143A5FA0A8A7D128@exchange2k"
type="cite">
<meta name="Generator"
content="MS Exchange Server version 6.0.6619.12">
<title>A proper date conversion function?</title>
<!-- Converted from text/rtf format -->
<p><font face="Arial" size="2">Hi everyone.</font>
<br>
<font face="Arial" size="2">This is interesting… there must be a good
date conversion function, using defines, out there that is better than
mine…</font>
</p>
<p><font face="Arial" size="2">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?</font></p>
<p><font face="Arial" size="2">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</font></p>
<p><font face="Courier New" size="2">;Get the date required, but
making sure that day and month</font>
<br>
<font face="Courier New" size="2">;is properly adjusted if they are
single digits. Format of the</font>
<br>
<font face="Courier New" size="2">;date has to come in with 'slash'
characters in the format: dd/mm/yyyy</font>
</p>
<p><font face="Courier New" size="2">define t_slash1 num*2 =
index(t_field_03,"/")</font>
<br>
<font face="Courier New" size="2">define t_mmyyyy char*7 =
t_field_03[(t_slash1 + 1):7]</font>
<br>
<font face="Courier New" size="2">define t_slash2 num*2 =
index(t_mmyyyy,"/")</font>
<br>
<font face="Courier New" size="2">define t_date_required_d char*2 =
t_field_03[1:(t_slash1 - 1)] &</font>
<br>
<font face="Courier New" size="2"> if t_slash1 > 2 and
t_rec_type = 1 &</font>
<br>
<font face="Courier New" size="2"> else "0" +
t_field_03[1:(t_slash1 - 1)] &</font>
<br>
<font face="Courier New" size="2"> if t_rec_type = 1</font>
<br>
<font face="Courier New" size="2">define t_date_required_m char*2 =
t_mmyyyy[1:(t_slash2 - 1)] &</font>
<br>
<font face="Courier New" size="2"> if t_slash2 > 2 and
t_rec_type = 1 &</font>
<br>
<font face="Courier New" size="2"> else "0" +
t_mmyyyy[1:(t_slash2 - 1)] &</font>
<br>
<font face="Courier New" size="2"> if t_rec_type = 1</font>
<br>
<font face="Courier New" size="2">define t_date_required_y char*4 =
t_mmyyyy[(index(t_mmyyyy,"/") + 1):4] &</font>
<br>
<font face="Courier New" size="2"> if t_rec_type = 1</font>
<br>
<font face="Courier New" size="2">define t_date_required_c char*8 =
pack(trunc(t_date_required_y) + &</font>
<br>
<font face="Courier New" size="2">
trunc(t_date_required_m) + &</font>
<br>
<font face="Courier New" size="2">
trunc(t_date_required_d)) &</font>
<br>
<font face="Courier New" size="2"> if
t_num_error <> "C" &</font>
<br>
<font face="Courier New" size="2"> and
t_rec_type = 1 &</font>
<br>
<font face="Courier New" size="2"> else "0"</font>
<br>
<font face="Courier New" size="2">define t_date_required date =
nconvert(t_date_required_c)</font>
</p>
<br>
<br>
</blockquote>
<pre class="moz-signature" cols="72">
</pre>
</body>
</html>