Oracle Database Discussions

I've successfully created a SQLLOADER script to upload data from a coma delimited file. Here is a sample of at least one line from the .csv file that I"ve modified for simplicity's sake.

Please note the date field columns. (the first column)

2011-05-04 04:13 GMT,13324161,JOHN, SMITH
2011-05-04 03:28 GMT,13324004,BOBBY,JONES
2011-05-04 00:44 GMT,13323358,GREG, HAMMER

My control file is as follows:

LOAD DATA
TRUNCATE
INTO TABLE TIME_AUDIT
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(
EMAIL_DATE "to_date(substr(:EMAIL_DATE,1,16), 'YYYY-MM-DD HH24:MI')",
USER_ID,
FIRST_NAME,
LAST_NAME
)

My problem lies with this line on SQL LOADER EMAIL_DATE "to_date(substr(:EMAIL_DATE,1,16), 'YYYY-MM-DD HH24:MI')",

No matter what - the date that I bring back is always in PM - NEVER in AM - some of those military dates are clearly a mixer of PM and AM. The script works perfectly - it's a matter of bringing back the date as PM or AM according to the time of the day.

I'm using a substr function so that I do not pick up the GMT which causes an error.

thanks,
Glenn