CODEWITHSUNDEEP
sqlstringoracle-databasedateoracle11g
Shulaz Shan
Shulaz Shan

Reputation: 175

How to convert number to date format?

I want to convert the number to date format. how to convert in oracle? Example: i have time key column(data type number) and want to create new columns as date

Time Key       Date
2018030100     01-Mar-2018

Upvotes: 0

Views: 94

Answers (2)

Atif
Atif

Reputation: 2210

You can make use of to_date and trim function.

select to_char(to_date(trim( trailing '0' from '2018030100'),'yyyymmdd'), 'dd-mon-yyyy') from dual;

If you have number field then you can:

select to_char(to_date(trim( trailing '0' from id),'yyyymmdd'), 'dd-mon-yyyy') from test_date;

Upvotes: 0

GMB
GMB

Reputation: 222492

Do you just want to_date()?

Assuming that the last two digits are always 00:

select to_date(my number / 100, 'yyyymmdd') as mydate 
from mytable

Or if they can have other values, but you always want to ignore them:

select to_date(round(my number / 100), 'yyyymmdd') as mydate 
from mytable

Upvotes: 1

Related Questions