0

Query:

SELECT date_parse(start_date, '%Y/%m/%d')
FROM sql_question_five_2 ;

date format looks like this in csv: 20210531 being read into table as string.

Have tried a few different things to get it to convert to a date YYYY-MM-DD

KatH.
  • 1
  • 2

2 Answers2

0

Your format string needs to match the source value. Your source doesn’t have /‘s in it so your format string shouldn’t either

NickW
  • 8,430
  • 2
  • 6
  • 19
  • Tried removing the /s to match source value as suggested: SELECT date_parse(start_date, '%Y%m%d') AS start_date FROM sql_question_five_2; Now it runs without error, but also no results? – KatH. Sep 22 '21 at 19:42
  • So what result do you get if you run: SELECT start_date, date_parse(start_date, '%Y%m%d') AS start_date1 FROM sql_question_five_2 – NickW Sep 22 '21 at 21:06
0

date_parse expects all inputs to look like the format string, if they don't it will throw an error. You can do something like this to avoid the problem:

SELECT IF(start_date = '', NULL, date_parse(start_date, '%Y/%m/%d'))
FROM sql_question_five_2

This guards against the case where the string is empty, which is the case when you get the error. If you have other strings that don't conform to the format you would have to guard against those too.

If that is the case you can use the TRY function which captures errors and returns NULL:

SELECT TRY(date_parse(start_date, '%Y/%m/%d'))
FROM sql_question_five_2
Theo
  • 131,503
  • 21
  • 160
  • 205
  • This worked once i removed the /s SELECT IF(start_date = '', NULL, date_parse(start_date, '%Y/%m/%d')) FROM sql_question_five_2 The results came back looking like this: 2019-01-17 00:00:00.000 My next step was to create a temp table to further format the date with this: SELECT DATE(start_date) AS start_date FROM "test"."question_five_temp_table"; Which gave me these results: 2021-01-01 – KatH. Sep 23 '21 at 19:23
  • The next part which im still trying to figure out is now to get a column with the difference in day from a start_date and end_date. I have tried DATEDIFF function, but Athena doesn't seem to recognize the function in the SELECT statement? SELECT DATE(start_date) AS start_date, DATE(end_date) AS end_date FROM "test"."question_five_temp_table" WHERE end_date >= CAST('2021-05-31' AS DATE) AND start_date <= CAST('2021-05-01' AS DATE); – KatH. Sep 23 '21 at 19:24