I want to find last 2 hours failed task from “sys.tasks” with below query but getting error message
Query: select created_time from sys.tasks where created_time >= CURRENT_TIMESTAMP - interval ‘2’ Hour
Error: unknown exception For input string “01T20:40:53:736Z”
sys.tasks (or generally any sys table) is notorious and stores all the time and date fields as string, hence you will not be able to use time functions for those fields.
Sr. Solutions Architect, Imply
Any alertnative sql for last 2 hours failed tasks.
I had a play around with CASTing and some string functions:
I couldn’t get the SUBSTRING function to work, so I used LEFT and RIGHT together instead to extract the timestamp. Note that this therefore does not take any timezones into account - so I’m guessing (???) that it will be in UTC.
Hopefully you can see where I was going, and therefore just add the WHERE clauses and then test it out.
CAST (LEFT(created_time,10) || ' ' || RIGHT(LEFT(created_time,19),8) AS TIMESTAMP) AS __time,
WHERE CAST (LEFT(created_time,10) || ' ' || RIGHT(LEFT(created_time,19),8) AS TIMESTAMP) > TIMESTAMPADD(HOUR, -2, CURRENT_TIMESTAMP)