Re: Sybase to Oracle date conversion

I wrote a simple sed and shell script combo to remove the milliseconds from the Sybase bcp file.

Sed script is:
s/:...AM/ AM/g
s/:...PM/ PM/g

ends up substituting a space for the :000 part of the date/time. Put in a file sed1.

the shell script is:
#!/bin/sh
infile=$1
bases=`basename $infile`
sed -f sed1 $bases'.bcp' > $bases'.bkp'

Called the script fixit, so ran it as:
fixit bcpname

Which found the .bcp file, then made a .bkp file, which is used by the load scripts.

Hope you found this helpful

Marc Calvert wrote:

> 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 Thu Aug 20 1998 - 10:41:59 CDT