Insert does not enter the date correctly even though it is in the default format

Question

I am trying to insert some data in SQL Server 2008 Express, in a newly created database, and a new table. When I execute the statement, it throws me an error saying the following:

Casting from varchar data type to smalldatetime produced an out-of-range value.

Am I making any mistake? The date format is the default format. Any ideas?

INSERT INTO WDL (grnr,kknr,lfnr,ktja,gebg,ktnr,kpnr,zszl,rsrn,chid,fpay,kcnr,res3,uknr,trnr,type,psnr) VALUES ('171114502', '161199999', '306', '2021-11-18 20:15:00', '2021-11-18 20:15:00', '171114497', '171114497', '0', '306', '29', '1', '16147256251234830750', '3', '161199999', '306','2', '29-1611-1-306')

Answer 1

The temporal datatype of SQL Server have two differents SQL ISO Standard value specifications that are :

'YYYYMMDD hh:mm:ss.nnn' for the DATETIME and SMALLDATETIME
'YYYY-MM-DD hh:mm:ss.nnnnnnn' for the DATETIME2, DATETIMEOFFSET and DATE

Why ? Because the standard has changed when the SQL:1999 version of the SQL language as been release. DATETIME dans SMALLDATETIME remains form Sybase that came from the early years of RDBMS area...

These two standard are not concerned by any settings of "culture"

DEMO

SET LANGUAGE French;

DECLARE @DTo DATETIME, @DTs SMALLDATETIME

SELECT @DTo = '20220131 23:59:59.987', @DTs = '20220131 23:59:59.987'

SELECT @DTo, @DTs

Note that @DTs shows a date of the next day due to rounding

DECLARE @DT2 DATETIME2, @D DATE, @DTZ DATETIMEOFFSET

SELECT @DT2 = '2022-01-31 23:59:59.9876543', 
       @D = '2022-01-31 23:59:59.9876543', 
       @DTZ =  '2022-01-31 23:59:59.9876543+01:00'

SELECT @DT2 , @D , @DTZ

Note that there is no more rounding for DATE datatype...