Ms sql ошибка 242

Insert it as '2012-11-16T00:00:00.000'

if you don’t need the time, insert it as

'20121116'

Those are the ISO formats for datetime/date respectively.
As with all other formats, you get problems depending on the language of the user that the SQL statement runs at.

You can also get problems like that with months/days, and it’s not always apparent.

e.g. if you parse a string like '01/01/2015', it will always work.
But when you get to '01/13/2015', you run into problems, because English uses month/day/year, while for example German uses day/month/year, and 13 is not a valid month…

In your case, it’s somehow misinterpreting time, and that can lead to all kinds of strange errors.

VALUES (convert(varchar,convert(datetime,{D '2019-06-30'}),102));

Starting from the inside, the ODBC escape sequence {D '2019-06-30'} returns a datetime.

(ignoring the redundant convert to datetime)

You’re then converting that to a string with 102 style (rather than 105 for Italian).

You’re then relying on an implicit conversion back to datetime to match the type of the target column.

The implicit conversion has a default style of 0 as you can see in the execution plan:

[Expr1003] = Scalar Operator(CONVERT_IMPLICIT(datetime,CONVERT(varchar(30),[@1],102),0))

(note: you should always specify the maximum length when using varchar)

When you use style 102 yyyy.mm.dd you must also set DATEFORMAT to YMD so SQL Server can parse the format under style 0.

When you use style 105 dd-mm-yyyy, you must set DATEFORMAT to DMY for the same reason.

The reason it works on one and not the other is the default DATEFORMAT for the language in each case.

See datetime, SET LANGUAGE, and Write International Transact-SQL Statements in the documentation.

Applications that use other APIs, or Transact-SQL scripts, stored procedures, and triggers should use the CONVERT statement with an explicit style parameter for all conversions between the time, date, smalldate, datetime, datetime2, and datetimeoffset data types and character string data types.

Also SQL Server DateTime Best Practices by Aaron Bertrand.

Hello everyone,

I have a strange issue here and i really do not know what i can do about it. We have a batch running every day on the SQL server that creates a new Job on the SQL server. This Job is scheduled to kickoff a DTS package and inside this DTS package there are
several stored procedures that will run and fill the tables with data.

Now this batch is running fine for about a year long now, until a week ago. Now this Job gives me an error message everytime it runs a specific stored procedure inside the DTS package. The error message is: 

Server: Msg 242, Level 16, State 3, Line 1
The conversion of a char data type to a datetime data
type resulted in an out-of-range datetime value.

Now the query where this happens is the following query:

insert into ShP_VBC_RAP_Verwerkt_Per_Medewerker
(
 Datum, Medewerker, Aantal, Status
)
select convert(varchar, created,101) datum, medewerker, count(*) aantal, status
from ShP_VBC_OudeWerklijst
group by medewerker, status, convert(varchar, created,101)
order by medewerker, status asc

Now this is just a query to fill some data that is used for reports and it is not really a complicated query. The ‘created’ and ‘Datum’ fields are DateTime fields inside the tables.

The strange part of this story is that when i run this query inside the Query Analyzer it runs fine, if i run the specific action inside the DTS package (for this query) it runs fine. The error message only happens when i run it in combination with the created
SQL Job…

Does anybody have any idea why this error only happens during my created Job and maybe what i can do about it?

Many thanks in advance!!

Vincent