formatDate returning a non-date format

Question & Answer
, , , ,
1

Hi,

I have a dataset where the datetimes have double quotes around the values. For example:

started_at example: “2023-05-16 12:06:31”
ended_at example: “2023-05-11 09:13:38”

I can convert both to datetimes using this code:

formatDate(parseDate(substring({started_at},2,19), ‘yyyy-MM-dd hh:mm:ss’), ‘yyyy-MM-dd hh:mm:ss’)
formatDate(parseDate(substring({ended_at},2,19), ‘yyyy-MM-dd hh:mm:ss’), ‘yyyy-MM-dd hh:mm:ss’)

I am trying to use the datediff function to find the difference between the two in seconds, but I am getting an error message that seems to suggest that the formatDate function is returning a varchar2000 instead of a date time. Is there any other way to convert two strings to datetimes and calculate their difference? If not, I’ll need to go upstream and reformat it at the ETL level.

Thank you in advance.

Here is the code:

dateDiff(
formatDate(parseDate(substring({started_at},2,19), ‘yyyy-MM-dd hh:mm:ss’), ‘yyyy-MM-dd hh:mm:ss’),
formatDate(parseDate(substring({ended_at},2,19), ‘yyyy-MM-dd hh:mm:ss’), ‘yyyy-MM-dd hh:mm:ss’),
“ss”
)

Here is the error message I get:
Expression dateDiff(
formatDate(parseDate(substring({started_at},2,19), ‘yyyy-MM-dd hh:mm:ss’), ‘yyyy-MM-dd hh:mm:ss’),
formatDate(parseDate(substring({ended_at},2,19), ‘yyyy-MM-dd hh:mm:ss’), ‘yyyy-MM-dd hh:mm:ss’),
“ss”
) for function dateDiff has incorrect argument type dateDiff(<VARCHAR(2000)>, <VARCHAR(2000)>, String). Function syntax expects Date, Date
Date, Date, String.

2

Try the following:

dateDiff(
  parseDate(substring({started_at}, 2, 19), 'yyyy-MM-dd HH:mm:ss'),
  parseDate(substring({ended_at}, 2, 19), 'yyyy-MM-dd HH:mm:ss'),
  "ss"
)
3

Thanks, but I get a different error message.

Expression dateDiff(
parseDate(substring({started_at}, 2, 19), ‘yyyy-MM-dd HH:mm:ss’),
parseDate(substring({ended_at}, 2, 19), ‘yyyy-MM-dd HH:mm:ss’),
“ss”
) for function dateDiff has incorrect argument type dateDiff(Date, Date, String). Function syntax expects Date, Date
Date, Date, String.

4

Hi @ilauters Checking in. Were you able to fix this issue or you still need help ?. If we do not hear back in the next 3 days, we will archive the question.