OraFAQ Forum: Server Utilities » Trim date characters within sql loader

Home » RDBMS Server » Server Utilities » Trim date characters within sql loader (Oracle RDBMS 10.2 RHEL4)

Show: Today's Messages :: Show Polls :: Message Navigator
E-mail to friend

Trim date characters within sql loader [message #474922]

Thu, 09 September 2010 12:32

TLegend33
Messages: 202
Registered: March 2005

Senior Member

I am attempting to insert date data into a column using sqlldr:

Here's the current format:

2010-03-01 00:20:19.277

So far, I haven't gotten anything to work. I would like to trim the .277 from the existing date. Here's my latest attempt:

birthdate DATE "to_date(substr(birthdate,1,19),'YYYY-MM-DD HH24:MI:SS')"

Here's what I'm expecting:

2010-03-01 00:20:19

Any ideas?
Thanks!

Report message to a moderator

Re: Trim date characters within sql loader [message #474924 is a reply to message #474922]

Thu, 09 September 2010 12:35

joy_division
Messages: 4267
Registered: February 2005
Location: East Coast USA

Senior Member

birthdate in double quotes required a colon in front of it.

what error are you getting?

Report message to a moderator

Re: Trim date characters within sql loader [message #474925 is a reply to message #474924]

Thu, 09 September 2010 12:38

TLegend33
Messages: 202
Registered: March 2005

Senior Member

birthdate DATE "to_date(substr(:birthdate,1,19),'YYYY-MM-DD HH24:MI:SS')"

ORA-01821: date format not recognized

[Updated on: Thu, 09 September 2010 12:48]

Report message to a moderator

Re: Trim date characters within sql loader [message #474929 is a reply to message #474925]

Thu, 09 September 2010 12:50

Barbara Boehmer
Messages: 7669
Registered: November 2002
Location: California, USA

Senior Member

birthdate "TO_DATE (SUBSTR (:birthdate,1,19), 'YYYY-MM-DD HH24:MI:SS')"

Report message to a moderator

Re: Trim date characters within sql loader [message #474930 is a reply to message #474925]

Thu, 09 September 2010 12:52

Michel Cadot
Messages: 54223
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

For DATE datatype the parameter between is a date format mask.
Try
DATE (19) "YYYY-MM-DD HH24:MI:SS"

Regards
Michel

Report message to a moderator

Re: Trim date characters within sql loader [message #474933 is a reply to message #474930]

Thu, 09 September 2010 12:58

TLegend33
Messages: 202
Registered: March 2005

Senior Member

birthdate DATE(19) "YYYY-MM-DD HH24:MI:SS"

Record 1: Rejected - Error on table DATA_TMP, column BIRTHDATE.
Field in data file exceeds maximum length

Report message to a moderator

Re: Trim date characters within sql loader [message #474937 is a reply to message #474933]

Thu, 09 September 2010 13:32

TLegend33
Messages: 202
Registered: March 2005

Senior Member

This did the trick, thanks everyone.

birthdate "to_date(substr(:birthdate,1,19),'YYYY-MM-DD HH24:MI:SS')"

Report message to a moderator

Previous Topic:	Oracle Data Pump
Next Topic:	EXPORT COMMAND IS NOT WORKING IN 11G THAT WOULD IN 8i

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu May 23 17:44:50 CDT 2013

Total time taken to generate the page: 0.66242 seconds