Search code examples
sqltimeformatutcsentence

Changing SQL VARCHAR2 date to another date format

I have a date (stored as a VARCHAR2) in a database with the format:

20090123_163842.865
yyyyMMdd_hhmmss.ttt

and I want to make a SQL sentence to obtain:

23/01/2009 16:38:42,865
dd/MM/yyyy hh:mm:ss,ttt

MY objective is to add it manually (I know that data can be exported from database, and imported into Excel, but I want to do it manually) to Excel as a recognizable Date.

How should my SQL sentence be?

I have tried to to it by:

select TO_TIMESTAMP(my_time_utc, 'YYYYMMDD_HH24MISS.FF3') from myTable

but I am only able to obtain:

2009-01-23 16:38:42.865

Thanks

Solution

  • It never ceases to amaze me how many people confuse these operations.

    First you need to convert the varchar 'fake date' to a real date: use to_date for this. Then you need to convert the date to a varchar for presentation: use to_char for this.

    select to_char(to_date(column, 'yyyyMMdd_hhmmss.ttt'), 'dd/MM/yyyy hh:mm:ss,ttt')
from your_table;

    should do what you want.