Re: Date format from YYYYDDD TO DD-MON-YYYY

[Richard Tomancikl <Richard_Tomancik@TIGINSURANCE.COM>]

This date issue is not deep one but it is something that you run into all
the time, like when an outside vendor wants an interface with the date in
their new favorite format.

If you use PeopleSoft, the datetime.sqc is used to stay platform
independent.  But their formats are limited. And its better to use the
platform specific functions to select and format the date the way you want
it.

The new cool thing I figured out today is that  you can use the 'select
from dual' on fields that are not even in 'dual' or any table.  Probably
not something you want to do all the time, but if you are converting
unusual (at least to me) like Julian concatenated with a year, it's quick.
See my SQR code below.

!***********************************************
begin-procedure main-proc
 let $date_text1 = '01-FEB-1999'
 let $date_text2 = '1998125'

begin-select
to_char( to_date( $date_text1, 'DD-MON-YYYY'), 'YYYYDDD SYEAR Q MONTH RM WW
 W D')   &test_date1
to_char( to_date( $date_text2, 'YYYYDDD'), 'DD-MON-YYYY')   &test_date2
from dual
end-select

 display '&test_date1: '  noline
 display &test_date1
 display '&test_date2: '  noline
 display &test_date2

end-procedure main-proc
!***********************************************

Result:
&test_date1: 1999032  NINETEEN NINETY-NINE 1 FEBRUARY  II   05 1 2
&test_date2: 05-MAY-1998

OK, so I got a little carried away with Oracles format options but you get
the idea.  RT