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
5 Answers
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)
2 Comments
abhi
but when I use
:: in Spring entity manager, it gives me error as syntax error at : or close to :
Gordon Linoff
@abhi . . .
:: is quite valid Postgres syntax and that is what your question is tagged. I don't know why a particular "framework" would not allow such code. But you can easily replace it with the equivalent cast().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
4 Comments
abhi
I get this error
ERROR: cannot cast type numeric to date Position: 18
Barbaros Özhan
excuse me, you're right. I just noticed that your value is of type numeric and needed to be converted to
varchar(of type text) first @abhi , since you already told that "I have a numeric data in a column 20170930".abhi
now I am getting
[22007] ERROR: invalid input syntax for type date: "20170930.000000"Barbaros Özhan
I've added the demo. I didn't understand what could be the problem @abhi
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.
answered Aug 26, 2019 at 6:37
Indrajith Ekanayake
4,38211 gold badges51 silver badges73 bronze badges
Comments
Please try below query
SELECT to_date(column::varchar,'YYYYMMDD')
Comments
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
DATE.