Home » RDBMS Server » Server Utilities » How to get date and time in the Date Column while sqlldr
icon5.gif  How to get date and time in the Date Column while sqlldr [message #556795] Wed, 06 June 2012 11:24 Go to next message
dora
Messages: 13
Registered: January 2012
Junior Member
Hi

I am not able to load complete date along with time in the date column.
here is my table desc

DESC STAGE
Name               Null     Type
----------------------------------
TABLE_NAME       NOT NULL   VARHCAR(20)
RECORDCOUNT                 NUMBER
CREATED_DATE     NOT NULL   DATE


my control file is like this

LOAD DATA
APPEND 
INTO TABLE SCOOP.STAGE
FIELDS TERMINATED BY ","
(   TABLE_NAME
   ,RECORDCOUNT
   ,CREATED_DATE DATE(16) "YYYYMMDDHH:Mi:SS"       
  )


the data gets loaded, but it appears like this in the table
HIGHSCHOOL 30806 06-JUN-12
MIDDLESCHOOL 87680 06-JUN-12

BUT I WANT COMPLETE DATE AND TIME (HH:MI:SS) , HOW CAN I GET IT (THIS IS HOW I WANT 06-JUN-12 11:07:33)

Thank You. Any help is appreciated.

Report message to a moderator

Re: How to get date and time in the Date Column while sqlldr [message #556797 is a reply to message #556795] Wed, 06 June 2012 11:37 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
select * from stage;

Report message to a moderator

Re: How to get date and time in the Date Column while sqlldr [message #556799 is a reply to message #556797] Wed, 06 June 2012 11:44 Go to previous messageGo to next message
dora
Messages: 13
Registered: January 2012
Junior Member
Hi Sir,

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
select * from stage;

These two statements when typed, it doesnot change, still i only see date no time in the date column of the table.

Any otherway of achieving it, pls reply.Thank You.

Report message to a moderator

Re: How to get date and time in the Date Column while sqlldr [message #556808 is a reply to message #556799] Wed, 06 June 2012 13:09 Go to previous messageGo to next message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
dora wrote on Wed, 06 June 2012 12:44
Hi Sir,

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
select * from stage;

These two statements when typed, it doesnot change, still i only see date no time in the date column of the table.

Any otherway of achieving it, pls reply.Thank You.


What data are you loading? I don't see any data. If the data doesn't have a time portion to it, what would you expect one to be loaded?

Report message to a moderator

Re: How to get date and time in the Date Column while sqlldr [message #556814 is a reply to message #556808] Wed, 06 June 2012 14:45 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
If I'm not wrong, BlackSwan thought that your current settings prevent you from seeing the time portion (assuming that it was loaded). Apparently, it wasn't even loaded.

You posted almost everything we need - table description, control file - but, unfortunately, no sample data. Could you post several sample records so that we would know what you have and, hopefully, assist?

Report message to a moderator

Re: How to get date and time in the Date Column while sqlldr [message #556815 is a reply to message #556814] Wed, 06 June 2012 14:49 Go to previous messageGo to next message
dora
Messages: 13
Registered: January 2012
Junior Member
Sir

the sample records are as follows:
HIGHSCHOOL,30806,2012060112:11:07
MIDDLESCHOOL,87680,2012060112:11:07

Sorry for the delay, I appreciate your help a lot.Thanks

Report message to a moderator

Re: How to get date and time in the Date Column while sqlldr [message #556819 is a reply to message #556815] Wed, 06 June 2012 15:01 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
In control file, modify the CREATED_DATE line to
CREATED_DATE "to_date(:created_date, 'YYYYMMDDHH:Mi:SS')"

and try to load data once again. What is the result?

Report message to a moderator

Re: How to get date and time in the Date Column while sqlldr [message #556826 is a reply to message #556819] Wed, 06 June 2012 15:18 Go to previous messageGo to next message
dora
Messages: 13
Registered: January 2012
Junior Member
Sir,

It still doesnot display time in that column. I am quering in SQL Developer and SQL Plus Worksheer

The result is same, just date example 06-JUN-12

no time in their.


I have changed my Control File, as mentioned.


LOAD DATA
APPEND 
INTO TABLE SCOOP.STAGE
FIELDS TERMINATED BY ","
(   TABLE_NAME
   ,RECORDCOUNT
   ,CREATED_DATE "to_date(:created_date, 'YYYYMMDDHH:Mi:SS')"   
      
  )


For now using 
select to_char(...............) , to see the date with time for that table.
But i would like to see the datewithtime when i just select from table without 
using any builtin and also when i see from SQL DEVELOPER.
Please help.Thanks

[Updated on: Wed, 06 June 2012 23:26] by Moderator

Report message to a moderator

Re: How to get date and time in the Date Column while sqlldr [message #556830 is a reply to message #556826] Wed, 06 June 2012 15:24 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>But i would like to see the datewithtime when i just select from table without using any builtin
>and also when i see from SQL DEVELOPER.

then change NLS_DATE_FORMAT for the whole database; but realize doing so could break existing applications.

Report message to a moderator

Re: How to get date and time in the Date Column while sqlldr [message #556831 is a reply to message #556830] Wed, 06 June 2012 15:30 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
dora
1) It still doesnot display time in that column
2) For now using select to_char(...............) , to see the date with time for that table.


The truth is: modified control file LOADED time portion into the column. SQL*Loader isn't responsible for the way you are fetching data FROM the table - it stores data INTO the table.

So, either change NLS_DATE_FORMAT (either for the whole database or for the current session - BlackSwan told you how to do that already) or use TO_CHAR function. Or, possibly, set date format mask in a tool you are using (if its options contain that option).

Report message to a moderator

Re: How to get date and time in the Date Column while sqlldr [message #556947 is a reply to message #556831] Thu, 07 June 2012 08:30 Go to previous message
dora
Messages: 13
Registered: January 2012
Junior Member
Thank You Sir.

Report message to a moderator

Previous Topic: How to associate a header record to its corresponding detail record
Next Topic: Import schema into a newly created user
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Mar 03 16:15:18 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.