Home » RDBMS Server » Server Utilities » Trim date characters within sql loader (Oracle RDBMS 10.2 RHEL4)
Trim date characters within sql loader [message #474922] Thu, 09 September 2010 12:32 Go to next message
TLegend33
Messages: 203
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!
Re: Trim date characters within sql loader [message #474924 is a reply to message #474922] Thu, 09 September 2010 12:35 Go to previous messageGo to next message
joy_division
Messages: 4544
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?
Re: Trim date characters within sql loader [message #474925 is a reply to message #474924] Thu, 09 September 2010 12:38 Go to previous messageGo to next message
TLegend33
Messages: 203
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 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8005
Registered: November 2002
Location: California, USA
Senior Member
birthdate "TO_DATE (SUBSTR (:birthdate,1,19), 'YYYY-MM-DD HH24:MI:SS')"
Re: Trim date characters within sql loader [message #474930 is a reply to message #474925] Thu, 09 September 2010 12:52 Go to previous messageGo to next message
Michel Cadot
Messages: 59751
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
Re: Trim date characters within sql loader [message #474933 is a reply to message #474930] Thu, 09 September 2010 12:58 Go to previous messageGo to next message
TLegend33
Messages: 203
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
Re: Trim date characters within sql loader [message #474937 is a reply to message #474933] Thu, 09 September 2010 13:32 Go to previous message
TLegend33
Messages: 203
Registered: March 2005
Senior Member
This did the trick, thanks everyone.
birthdate "to_date(substr(:birthdate,1,19),'YYYY-MM-DD HH24:MI:SS')"

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


Current Time: Sun Nov 23 16:52:33 CST 2014

Total time taken to generate the page: 0.08424 seconds