Re: Sybase to Oracle date conversion

Thanks for the responce. The date format 'Mon dd yyyy hh12:mi:ss:000AM' returns the dreaded ORA-01821 Date format not recognized from the SQL* Loader. I am using a Sun Solaris 2.6 and Oracle 8.0.5 system. I am down to using awk to convert the all of the PM dates to 24 hour format. The BCP unload was in character format with a tilde as the field delimiter. Here is my awk code. Its been a long time.

BEGIN { FS = "~" }
{ for ( i = 1; i < (NF+1); ++i ) { cfld = $i ; pos = index($i,":000PM");
if ( pos != 0 ) { dt = substr(cfld,1,12); hr = substr(cfld,13,2); mi = substr(cfld,15,12); if ( hr == 12 ) { nhr=" 0" } else { nhr=hr+12 };
ndt=dt nhr mi ; if ( i ==NF )

{ printf "%s%s%s", ndt,FS,ORS } else
{ printf "%s%s", ndt,FS }} else if ( i == NF )
{ printf "%s%s%s", cfld,FS,ORS } else { printf "%s%s", cfld,FS }}}

To use put all of the code from the second line down on one line.

prochak_at_my-dejanews.com wrote:

> In article <6qs7d5$o9g_at_newsops.execpc.com>,
> ahc2_at_execpc.com wrote:
> > The Sybase BCP program unloads dates in the format Mon dd yyyy
> > hh12:mi:ss:000AM or PM . How can I get the Sql*Loader to recognize the
> > AM or PM to load the date properly. If I use the substr function every
> > date loads as an AM date.
> >
>
> Is there a reason why you do not use the AM formatting option of the
> TO_DATE function? you shouldn't trim off the AM/PM. It looks like you need:
>
> fieldname DATE 'MON DD YYYY hh12:mi:ss:000AM'
>
> in your control file. Or is that what you are already doing? I must be
> misreading your question because this seems too simple.
>
> -----== Posted via Deja News, The Leader in Internet Discussion ==-----
> http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
Received on Fri Aug 14 1998 - 09:49:41 CDT