Search code examples
sqldateselectoracle11gvarchar2

Convert datatype to Date format

I am unable to make data type of a column as DATE while creating view, default data type is varchar2. How can I change it to DATE column.

Below is the column definition:

TO_CHAR(DATE'1970-01-01' + ( 1465484488329 / 86400000 ), 'YYYY/MM/DD HH24:MI:SS') AS StartTime

Output will be

2016/06/09 15:01:28

but it is in Varchar2 format as default. Please suggest how to make it as Date column type.

Solution

  • to_char returns a varchar. Just omit it, and you should have a date:

    DATE'1970-01-01' + ( 1465484488329 / 86400000 )