| date format [message #36001] |
Mon, 29 October 2001 19:45  |
Henning
Messages: 6
Registered: October 2001
|
Junior Member |
|
|
I have a text field with a date in this format.
yyyymmdd
and a text field with a time in this format
hhmiss
What would be the best(most efficient) way of creating a date field in this format
dd-mon-yyyy hh:mi:ss
I have several millions records in the table
regards
Henning
----------------------------------------------------------------------
|
|
|
|
|
|
| Re: date format [message #36022 is a reply to message #36001] |
Tue, 30 October 2001 07:15  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
-- add a new column of a date datatype
alter table t add (datecol date);
-- populate this new column using the existing text date and time columns
-- since you have millions of rows, you may not be able to run this update in a single statement depending on the size of your rollback segments
update t
set datecol = to_date(textdate || texttime, 'yyyymmddhh24miss');
-- now you can format the date column any way you want
select to_char(datecol, 'dd-mon-yyyy hh:mi:ss am') from t;
-- optionally, you can now drop the two text columns and store any new dates in datecol
Does this help?
----------------------------------------------------------------------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
