Convert char time stamp to date

[Ratzie]

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.

[A. Clay Stephenson]

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 ...

[Ratzie]

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]

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

[Peter Godron]

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]

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)

[Kyris]

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]

Hi,
could you please update the thread. Any progress/solution?

[Hein van den Heuvel]

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]

We have decided to stick with the CHAR field.