cancel
Showing results for 
Search instead for 
Did you mean: 

Convert char time stamp to date

Ratzie
Super Advisor

Convert char time stamp to date

We have an older database that has a data type of char(14)
It it listed in the database as:
Basically an appt. end time.
2030 0830 PM
This is one field. Basically two time stamps meaning the same thing. First is in 24 hr time, and the second is in 12 time.

We would like to convert this data type to a day format. And just stick with first heading of 24 hr time stamp. (2030)
When it was tried it change it to a date stamp of April 1/06

How would you convert this char data type to just a time stamp.

9 REPLIES
A. Clay Stephenson
Acclaimed Contributor

Re: Convert char time stamp to date

It's rather difficult to help you when you don't bother to identify the database nor do you indicate if you simply want hours and minutes or if you want dates as well. I suppose I could use The Force to determine these things but ...
If it ain't broke, I can fix that.
Ratzie
Super Advisor

Re: Convert char time stamp to date

Data will be going into a Oracle 10g
We would like it to be HH24:MN

But 2030 0830 PM some how gets converted to April 1/06
Rodney Hills
Honored Contributor

Re: Convert char time stamp to date

If the old data is sitting in a text file and you would like HH:MM format, then perl could do it easily-

perl -p -e '/^(\d\d)(\d\d) / && print "$1:$2\n"' yourolddata.txt

HTH

-- Rod Hills
There be dragons...
Peter Godron
Honored Contributor

Re: Convert char time stamp to date

Hi,
The Oracle date type is used to store full dates (i.e. year,day,moth,hour,minute,second)

If your database only has the time stored, all the data is for the same day?

If so you could do hardcode:
select to_date('26/04/06 '||substr(field1,1,4),'DD/MM/YY HH24MI') from test;
Frank de Vries
Respected Contributor

Re: Convert char time stamp to date

Why not use a straigh forwar char formatter function from Oracle ?

select to_char(sysdate,'DD/MM/YY-HH24:MI') from dual

(Replace sysdate by your column and dual with your table, but you can use the above example to test your format)
Look before you leap
Kyris
Frequent Advisor

Re: Convert char time stamp to date

I am not sure exactly what is required. Are you trying to merely write an SQL statement/report or changing your table structure.
Also is there an DD-MON-YYYY part to this or is it not required?
The to_char(colname,'HH24:MI') will be ok or in a table colname DATE "HH24:MI" should work.
Hope this is helpfull.
kyris
Peter Godron
Honored Contributor

Re: Convert char time stamp to date

Hi,
could you please update the thread. Any progress/solution?
Hein van den Heuvel
Honored Contributor

Re: Convert char time stamp to date

I agre with Peter and others that a to_date , based on substr(field1,1,4) using format 'DD/MM/YY HH24MI' is in order.

You may want to simply do this 'all the time' on retrieval (through a view?) or conveert the table adding a new column, sturing the calculated values in that column, and dropping the old varchar holding the funky time string.

My choice woudl probably a fresh create table as...
Rename the tables when done.

Good luck,
Hein.

Ratzie
Super Advisor

Re: Convert char time stamp to date

We have decided to stick with the CHAR field.