convert int to date in redshift

user_16344077 0
Viewed 659

I have column named datenum which is int type. For ex. 20220208 Need to convert datenum column type to date with YYYY/MM/DD format. Tried to_date("datenum", 'YYYY/MM/DD') but isn't working.

amazon-redshift sql
1 Answers

Dates in Redshift (or any SQL database) do not really have any internal format. To convert your input text dates to bona fide Redshift dates, use TO_DATE with the appropriate date mask:

SELECT TO_DATE(datenum, 'YYYYMMDD')
FROM yourTable;

If you really want to view your input dates in some other text format, then do a full roundtrip back to string, using TO_CHAR:

SELECT TO_CHAR(TO_DATE(datenum, 'YYYYMMDD'), 'YYYY/MM/DD')
FROM yourTable;
user_18632290
Related