Convert Number type to Date in postgres SQL

Question

I have a numeric data in a column 20170930, need help in converting it into Date in PostgreSQL , tried multiple ways but non seems to work

Answer 1

You can convert to a string and then to a date:

select column::text::date

You can also express this using explicit cast() syntax:

select cast(cast(20170930 as text) as date)

Answer 2

Use one of the following :

SELECT cast(yourcol::varchar as date ) as dt1, yourcol::varchar::date as dt2

where dt1 and dt2 values of type date, and yourcol is a numeric value such as 20170930

Demo

Answer 3

The best thing is to change column datatype into Date type,

  ALTER TABLE table_name
    ADD column_name Date;

As shown above, PostgreSQL supports a full set of SQL date and time types, as shown in the table below. Dates are counted according to the Gregorian calendar. Here, all the types have a resolution of 1 microsecond / 14 digits except date type, whose resolution is day.

Answer 4

Please try below query

SELECT to_date(column::varchar,'YYYYMMDD') 

Answer 5

For anybody who fell into my pitfall I tried this but my numeric was like a 'seconds past 01-01-1970 format' rather than YYYYMMDD

This worked

SELECT to_timestamp(yourcol) as numeric_column_now_date
from yourtable

see here https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-ZONECONVERT