| Problem converting dates with SQL Loader [message #73360] |
Thu, 01 April 2004 12:59  |
cswords
Messages: 5
Registered: April 2004
|
Junior Member |
|
|
Help! I am using SQL Loader to import data into Oracle 9.2 which was exported from Sybase (via BCP). I have a field in the exported file which looks like this:
Mar 31 2004 2:41:28:000PM
What format string should I use to convert this string into a DATE field via the ctl file? I tried the following, but kept getting "ORA-01821: date format not recognized". (assume the date field is named "createdDate").
createdDate DATE(23) "MON DD YYYY HH:MI:SS:FF3AM"
createdDate DATE "MON DD YYYY HH:MI:SS:FF3AM"
Is FF# right for specifying fractions of a second, with # being the percision?
Is AM right for specifying AM or PM?
Thanks!
|
|
|
|
| Re: Problem converting dates with SQL Loader [message #73367 is a reply to message #73360] |
Fri, 02 April 2004 03:22  |
 |
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
You can either change your createdDate column to a TIMESTAMP datatype and use the following in your SQL*Loader control file:
createDate TIMESTAMP "Mon dd yyyy hh:mi:ss:ff3AM"
or you can leave your createdDate column as a DATE datatype and create a function to convert it, then use that function in your SQL*Loader control file, like so:
-- create function from SQL*Plus:
CREATE OR REPLACE FUNCTION my_to_date
(p_string IN VARCHAR2)
RETURN DATE
AS
BEGIN
RETURN TO_DATE ((SUBSTR (p_string,
1,
INSTR (p_string, ':', -1) - 1)
|| SUBSTR (p_string,
INSTR (p_string, ':', -1) + 4)),
'Mon dd yyyy fmhh:mi:ssAM');
END my_to_date;
/
-- in SQL*Loader control file:
createdDate "my_to_date (:createdDate)"
|
|
|
|
| Re: Problem converting dates with SQL Loader [message #73369 is a reply to message #73367] |
Fri, 02 April 2004 07:14 |
cswords
Messages: 5
Registered: April 2004
|
Junior Member |
|
|
Barbara,
I couldn't change the datatype to TIMESTAMP (table layout used by others) so I used the conversion function you provided.... it worked like a charm!!
I was wondering, though, what the "fm" is for in the format string - I couldn't find any documentation on that.
Lastly, how do I view the the function? If I use DESCRIBE, it only returns the parameters but not the "code".
Thanks SOOO much!
Christina
|
|
|
|
| Re: Problem converting dates with SQL Loader [message #73370 is a reply to message #73369] |
Sat, 03 April 2004 04:03 |
|
Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Actually, the fm isn't necessary. You can remove it and it will work the same either way. I included it because I noticed that your data did not have a leading zero in front of the hours. Please see the examples below, with and without fm and notice that with fm the hours do not have a leading zero.
scott@ORA92> select to_char (sysdate, 'Mon dd yyyy hh:mi:ss') from dual;
TO_CHAR(SYSDATE,'MON
--------------------
Apr 03 2004 05:50:35
scott@ORA92> select to_char (sysdate, 'Mon dd yyyy fmhh:mi:ss') from dual;
TO_CHAR(SYSDATE,'MON
--------------------
Apr 03 2004 5:50:54
Here is an excerpt from Oracle on-line documentation that explains how fm (fill mode format model modifier) works:
"FM
"Fill mode". This modifier suppresses blank padding in the return value of the TO_CHAR function:
In a datetime format element of a TO_CHAR function, this modifier suppresses blanks in subsequent character elements (such as MONTH) and suppresses leading zeroes for subsequent number elements (such as MI) in a date format model. Without FM, the result of a character element is always right padded with blanks to a fixed length, and leading zeroes are always returned for a number element. With FM, because there is no blank padding, the length of the return value may vary.
In a number format element of a TO_CHAR function, this modifier suppresses blanks added to the left of the number, so that the result is left-justified in the output buffer. Without FM, the result is always right-justified in the buffer, resulting in blank-padding to the left of the number. "
To view the source code of the function:
scott@ORA92> SELECT text
2 FROM user_source
3 WHERE name = 'MY_TO_DATE'
4 AND type = 'FUNCTION'
5 ORDER BY line;
TEXT
---------------------------------------------------------------------
FUNCTION my_to_date
(p_string IN VARCHAR2)
RETURN DATE
AS
BEGIN
RETURN TO_DATE ((SUBSTR (p_string,
1,
INSTR (p_string, ':', -1) - 1)
|| SUBSTR (p_string,
INSTR (p_string, ':', -1) + 4)),
'Mon dd yyyy hh:mi:ssAM');
END my_to_date;
12 rows selected.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
