Using Amazon Athena, I am working with a set of data stored as variable characters and would like to convert them to dates. There are two columns within a table that have dates: (1) action_date and (2) pricing_date.
With action_date, I have been able to successfully convert the original data using the dateparse function with the following query:
SELECT date_parse(s.action_date,'%m/%d/%Y %H:%i:%s') AS dataconverted
FROM "database"."sales" s
With pricing_date, I am having difficulties doing the same despite the data being in the same format. I would assume that the query should be the same. Following is my query:
SELECT date_parse(s.pricing_date,'%m/%d/%Y %H:%i:%s') AS dataconverted
FROM "mydatabase"."sales" s
Following is the error I get in Amazon Athena:
Your query has the following error(s):
[ErrorCategory:USER_ERROR, ErrorCode:INVALID_ARGUMENT], Detail:INVALID_FUNCTION_ARGUMENT: Invalid format: ""
This query ran against the "mydatabase" database, unless qualified by the query.
How I can convert the successfully convert the variable character text into a date format? What could I possibly be missing?
From the error it looks like the pricing_date column sometimes is an empty string. date_parse will throw an error if the input is not on the specified formatat. You can observe this by running SELECT date_parse('', '%m/%d/%Y %H:%i:%s') or SELECT date_parse('asdasd','%m/%d/%Y %H:%i:%s').
You can work around this by adding a guard (e.g. IF(s.pricing_date <> '', date_parse(…), NULL)) or by wrapping the call in TRY, which results in NULL if there was an error:
SELECT try(date_parse(s.pricing_date,'%m/%d/%Y %H:%i:%s')) AS dataconverted
FROM "mydatabase"."sales" s