3

I created a small script that works in SQL Server Management Studio, then I created a SQL Server Agent job with that same script; but after I run it, I get an error:

The conversation of a varchar data type to a datetime data type resulted in an out-of-range

I fixed the problem by change the format of date to ISO 8601, but I don't release how my first script works on SSMS and not in SQL Server Agent.

First script:

declare @teste datetime
set @teste = '31/12/2099 00:00:00'
select @teste

Fix error:

declare @teste datetime
set @teste = '20991231 00:00:00'
select @teste
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Sacamoto
  • 107
  • 1
  • 8

1 Answers1

2

This is one reason why using unambiguous formats are so important when using date(time) datatypes. The only formats that an unambgious in SQL Server, regardless of language and datatype are the formats yyyyMMdd and yyyy-MM-ddThh:mm:ss.

For the date '31/12/2099 00:00:00' and the language your Login is using it appears that SQL Server is interpreting the value as the 12th day, of the 31st month, of the year 2099. There aren't 31 months in the year, and hence the error. (DB<>Fiddle). It's worth noting that date (and the other "new" datetime data types) behave differently and also can unambiguously understand the format yyyy-MM-dd; notice in this DB<>fiddle the difference in the values for the datetime values simply due to the language setting.

As you can see, the solution is to use a unambiguous format. So, as you're using a date and time, I would suggest the string '2099-12-31T00:00:00'.

Thom A
  • 88,727
  • 11
  • 45
  • 75