SQL Unable to Insert Converted DateTime in Column

Question

I am puzzled why I am unable to insert a converted date value into my table column when the output of it is the same.

Error:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: Conversion failed when converting date and/or time from character string

Success (hard coded DateTime):

cmd.CommandText = @"INSERT INTO [TestDB].[Cat1].[Table1] (CreatedOn) 
                    VALUES ('2019-08-22 23:59:59.000')";

Failed (converted DateTime):

cmd.CommandText = @"INSERT INTO [TestDB].[Cat1].[Table1] (CreatedOn) 
                    VALUES ('@CreatedOn')";

//value below has the same output as above (2019-08-22 23:59:59.000)
cmd.Parameters.AddWithValue("@CreatedOn", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));

//below failed too
cmd.Parameters.AddWithValue("@CreatedOn", DateTime.Now);

Column Type:

Answer 1

The value '2019-08-22 23:59:59.000' is not unambiguous with the datetime datatype (it is with the new date and time datatypes). If, for example, you're English then the below will fail:

SET LANGUAGE BRITISH;
SELECT CONVERT(datetime,'2019-08-22 23:59:59.000');

This is because SQL Server reads the date in the format yyyy-dd-MM, and there aren't 22 months in the year. As noted though, it works fine with the newer data types, or if you're American (but you should be writing language agnostic code):

SET LANGUAGE BRITISH; --English
SELECT CONVERT(datetime2(3),'2019-08-22 23:59:59.000');
SELECT CONVERT(datetimeoffset(0),'2019-08-22 23:59:59.000');

SET LANGUAGE ENGLISH; --American. Confusing, right? :)
SELECT CONVERT(datetime,'2019-08-22 23:59:59.000');

One way is to use an unambiguous format, which isn't affected by data type or language:

SET LANGUAGE BRITISH;
SELECT CONVERT(datetime,'2019-08-22T23:59:59.000');

Otherwise you can tell SQL Server the style of the value (in this case 121):

SET LANGUAGE BRITISH;
SELECT CONVERT(datetime,'2019-08-22 23:59:59.000',121);