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

Question

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?

Answer 1

I'd say the problem is related to the language being used when the query runs.

Please, run this from SSMS and from the job you created to check if the language being used is the same on both methods:

SELECT @@LANGUAGE AS 'Language Name';

Your query failed when I ran even from SSMS with the same error and the reason was the date format causing 31/08 to be an invalid date whereas 08/31 worked properly. My language is currently us_english.

If needed, the language of the session can be set using SET LANGUAGE command.

Answer 2

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.