OPunktSchmidt OPunktSchmidt - 4 days ago 5
SQL Question

Conversion of a varchar data type to a datetime. Out-of-range error

I have a strange error with my SQL Query.

This works:

INSERT INTO tbl_SomeTable(Send_Type,Remote_Eml,Cover_Subj,Cover_Note,AtnId,StandortEinsatzValue,Standort,KundenNr,AtnTel,Name,Strasse,AtnName,AreaManagerContact,Datum,PLZOrt)
VALUES ('2', '', '', '','02832','1','Replaced','2001158207','Replaced','Real','Replaced','Replaced','Replaced','10.12.2016','15745 Replaced')


This gives me an error:

INSERT INTO tbl_SomeTable(Send_Type,Remote_Eml,Cover_Subj,Cover_Note,AtnId,StandortEinsatzValue,Standort,KundenNr,AtnTel,Name,Strasse,AtnName,AreaManagerContact,Datum,PLZOrt)
VALUES ('2', '', '', '','02832','1','Replaced','2001158207','Replaced','Real','Replaced','Replaced','Replaced','17.12.2016','15745 Replaced')



The conversion of a varchar data type to a datetime data type resulted
in an out-of-range value.


Only difference is the date. "10.12.2016" vs "17.12.2016".
Column "Datum" is of type "datetime".

We use a SQL Server 2016.

Can you help me?

Answer

You need to use CONVERT for this. Replace your queries as below.

Also maintain a standardised date formats in the DB.

INSERT INTO tbl_SomeTable(Send_Type,Remote_Eml,Cover_Subj,Cover_Note,AtnId,StandortEinsatzValue,Standort,KundenNr,AtnTel,Name,Strasse,AtnName,AreaManagerContact,Datum,PLZOrt)
VALUES ('2', '', '', '','02832','1','Replaced','2001158207','Replaced','Real','Replaced','Replaced','Replaced',CONVERT(DATETIME,'10.12.2016',103),'15745 Replaced')

INSERT INTO tbl_SomeTable(Send_Type,Remote_Eml,Cover_Subj,Cover_Note,AtnId,StandortEinsatzValue,Standort,KundenNr,AtnTel,Name,Strasse,AtnName,AreaManagerContact,Datum,PLZOrt)
VALUES ('2', '', '', '','02832','1','Replaced','2001158207','Replaced','Real','Replaced','Replaced','Replaced',CONVERT(DATETIME,'17.12.2016',103),'15745 Replaced')
Comments