<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 3.2//EN">
<HTML>
<HEAD>
<META NAME="Generator" CONTENT="MS Exchange Server version 6.0.6619.12">
<TITLE>A proper date conversion function?</TITLE>
</HEAD>
<BODY>
<!-- Converted from text/rtf format -->
<P><FONT SIZE=2 FACE="Arial">Hi everyone.</FONT>
<BR><FONT SIZE=2 FACE="Arial">This is interesting… there must be a good date conversion function, using defines, out there that is better than mine…</FONT>
</P>
<P><FONT SIZE=2 FACE="Arial">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 SIZE=2 FACE="Arial">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 SIZE=2 FACE="Courier New">;Get the date required, but making sure that day and month</FONT>
<BR><FONT SIZE=2 FACE="Courier New">;is properly adjusted if they are single digits. Format of the</FONT>
<BR><FONT SIZE=2 FACE="Courier New">;date has to come in with 'slash' characters in the format: dd/mm/yyyy</FONT>
</P>
<P><FONT SIZE=2 FACE="Courier New">define t_slash1 num*2 = index(t_field_03,"/")</FONT>
<BR><FONT SIZE=2 FACE="Courier New">define t_mmyyyy char*7 = t_field_03[(t_slash1 + 1):7]</FONT>
<BR><FONT SIZE=2 FACE="Courier New">define t_slash2 num*2 = index(t_mmyyyy,"/")</FONT>
<BR><FONT SIZE=2 FACE="Courier New">define t_date_required_d char*2 = t_field_03[1:(t_slash1 - 1)] &</FONT>
<BR><FONT SIZE=2 FACE="Courier New"> if t_slash1 > 2 and t_rec_type = 1 &</FONT>
<BR><FONT SIZE=2 FACE="Courier New"> else "0" + t_field_03[1:(t_slash1 - 1)] &</FONT>
<BR><FONT SIZE=2 FACE="Courier New"> if t_rec_type = 1</FONT>
<BR><FONT SIZE=2 FACE="Courier New">define t_date_required_m char*2 = t_mmyyyy[1:(t_slash2 - 1)] &</FONT>
<BR><FONT SIZE=2 FACE="Courier New"> if t_slash2 > 2 and t_rec_type = 1 &</FONT>
<BR><FONT SIZE=2 FACE="Courier New"> else "0" + t_mmyyyy[1:(t_slash2 - 1)] &</FONT>
<BR><FONT SIZE=2 FACE="Courier New"> if t_rec_type = 1</FONT>
<BR><FONT SIZE=2 FACE="Courier New">define t_date_required_y char*4 = t_mmyyyy[(index(t_mmyyyy,"/") + 1):4] &</FONT>
<BR><FONT SIZE=2 FACE="Courier New"> if t_rec_type = 1</FONT>
<BR><FONT SIZE=2 FACE="Courier New">define t_date_required_c char*8 = pack(trunc(t_date_required_y) + &</FONT>
<BR><FONT SIZE=2 FACE="Courier New"> trunc(t_date_required_m) + &</FONT>
<BR><FONT SIZE=2 FACE="Courier New"> trunc(t_date_required_d)) &</FONT>
<BR><FONT SIZE=2 FACE="Courier New"> if t_num_error <> "C" &</FONT>
<BR><FONT SIZE=2 FACE="Courier New"> and t_rec_type = 1 &</FONT>
<BR><FONT SIZE=2 FACE="Courier New"> else "0"</FONT>
<BR><FONT SIZE=2 FACE="Courier New">define t_date_required date = nconvert(t_date_required_c)</FONT>
</P>
<BR>
<BR>
<pre>**********************************************************************
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@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
**********************************************************************
</pre></BODY>
</HTML>