Why does this query fail when I run it from a SQL Server job?

I know how to solve the issue, but I don't understand the technicality of why the below query would work when I run it from SSMS, but the same query fails when I add it as a job in the SQL Server agent.

The query:

select LEFT(datediff(day,GETDATE(),'31/08/20'+'26')/365.25,2) AS test

The error I'm getting when the job fails:

Conversion failed when converting date and/or time from character
string. [SQLSTATE 22007] (Error 241). The step failed.

Does the SQL agent use a different engine or different techniques?

It fails because you aren't using a language neutral datetime format. You are assuming that SQL Server understands that the day comes first, then the month and finally the year.

When you run this interactively using SSMS, you are using a login (as in the CREATE LOGIN command) that has a dateformat the correspond to that order. I.e., your login's dateformat is dmy.

Agent is using a different datetime format for its login, evidently. That is why it fails. Perhaps it is mdy (the default for us_english).

What you want to do is to use a datetime format that doesn't depend on the datetime setting for the login - what I like to call a "language neutral" datetime format. The YYYYMMDD is one such format. Here's your expression converted to that format:

select LEFT(datediff(day,GETDATE(),'20' + '26' + '0831')/365.25,2) AS test

Another option is to add SET DATEFORMAT before your expression:

set dateformat dmy
select LEFT(datediff(day,GETDATE(),'31/08/20'+'26')/365.25,2) AS test

Here's an article regarding datetime that I have written: https://karaszi.com/the-ultimate-guide-to-the-datetime-datatypes

I wouldn't mess about with Agent's language! You never know what might break. Just use the dateformat command or (preferably) use language neutral datetime format.

StackExchange Database Administrators Q#310112, answer score: 12