Home » SQL & PL/SQL » SQL & PL/SQL » date format
date format [message #36001] Mon, 29 October 2001 19:45 Go to next message
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 #36015 is a reply to message #36001] Tue, 30 October 2001 05:26 Go to previous messageGo to next message
Suresh Vemulapalli
Messages: 624
Registered: August 2000
Senior Member
date format

----------------------------------------------------------------------
Re: date format [message #36022 is a reply to message #36001] Tue, 30 October 2001 07:15 Go to previous message
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?

----------------------------------------------------------------------
Previous Topic: Re: SELECT with variables
Next Topic: SELECT with variables
Goto Forum:
  


Current Time: Mon Oct 26 14:53:23 CDT 2020