CODEWITHSUNDEEP
sqldatetype-conversionformatsnowflake-cloud-data-platform
Yanzal
Yanzal

Reputation: 113

Snowflake SQL convert date 'YYYY-MM-DD' to 'DD-MM-YYYY'

I have a table in which date is stored in a dimension table. I am using this table to retrieve the latest reporting week.

SELECT MAX("Week") AS "Date" FROM "DWH"."DimWeek"

This returns a table with the following date that is in 'YYYY-MM-DD'

+--------------------+
| Date               |
|--------------------+
| 2017-01-03         |
+--------------------+

I wish to convert this date, so it returns a format of 'DD-MM-YYYY'

I have attempted to use

SELECT TO_DATE(MAX("Week"), 'DD-MM-YYYY') AS "Date" FROM "DWH"."DimWeek"

SQL Error
too many arguments for function [TO_DATE(MAX("Week", 'DD-MM-YYYY')] expected 1, got 2

I have also attempted to convert it to CHAR

SELECT TO_DATE(TO_CHAR(MAX("Week")), 'DD-MM-YYYY') AS "Date" FROM "DWH"."DimWeek"

However this also returns the result in the undesired format

+--------------------+
| Date               |
|--------------------+
| 2017-01-03         |
+--------------------+

Any tips or ideas? Currently querying from Snowflake SQL

Upvotes: 1

Views: 8175

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

Use TO_CHAR(). You want a string in the result, not a date:

SELECT TO_CHAR(MAX("Week")), 'DD-MM-YYYY') AS Date
FROM "DWH"."DimWeek"

Upvotes: 3

Related Questions