0

I am trying to build a query in SQL Server 2019 to return data for events that are greater than 2 days, but there is no specific datetime field. The only field with datetime data within it also has other information. I used SUBSTRING to pull only the date time info from the field, but the query is still not working.

Here is what the last_success_date field looks like after using SUBSTRING: 2022-08-07T23:03:36-07:00 I've tried also limiting it to only the year/month/day return.

SELECT 
    resource_key
    , SUBSTRING(last_actions,3,13) as 'Action'
    , SUBSTRING(last_success,actions,27,25) as 'Last_Success_Date'
    , applied_policy_names
from resources_status
where 'Last_Success_Date' >= DATEADD(DAY, -2,GETDATE())*

With this I am getting:

Conversion failed when converting date and/or time from character string.

Any ideas?

Dale K
  • 25,246
  • 15
  • 42
  • 71
Draconus77
  • 23
  • 3
  • 2
    `'Last_Success_Date'` isn't a date and time value, it's a literal string. – Thom A Aug 08 '22 at 21:17
  • 1
    This is one reason why you shouldn't use single quotes (`'`) for aliases. Single quotes are for literal strings, not delimit identifying object names. They only work when you define them, no where else. Also some syntaxes with literal string aliases are deprecated. Stick to object and alias names that don't need delimit identifying, and if you *must* delimit identify them use the T-SQL identifier, brackets (`[]`), or ANSI-SQL's, double quotes (`"`). – Thom A Aug 08 '22 at 21:18
  • 3
    Also fix the design, you shouldn't have to extract date/time values out of a string. So many ways for that to go sideways. – Aaron Bertrand Aug 08 '22 at 21:18
  • 1
    You can't reference in the where clause an alias you _just created_ in the select clause. You can try `WHERE SUBSTRING(last_success,actions,27,25) >= DATEADD...` but I think you need more safety around that (at least `TRY_CONVERT`). – Aaron Bertrand Aug 08 '22 at 21:19

0 Answers0