CODEWITHSUNDEEP
sqlamazon-redshift
ms93
ms93

Reputation: 49

convert int to date in redshift

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.

Upvotes: 2

Views: 4967

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

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;

Upvotes: 2

Related Questions