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:

https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=d6c32d05795580d94d19b85fedc57031 your quesry working fine check your column datatype — Zaynul Abadin Tuhin, Aug 22 '19 at 06:55
`VALUES ('@CreatedOn')"` are you sure? It should be `cmd.CommandText = @"INSERT INTO [TestDB].[Cat1].[Table1] (CreatedOn) VALUES (@CreatedOn)";` no quotes around the param name. — Serg, Aug 22 '19 at 07:22
try specifying datatype, cmd.Parameters.Add("@CreatedOn", SqlDbType.DateTime).Value = DateTime.Now; — Jayant Bramhankar, Aug 22 '19 at 07:59
You *assume* the OP is American @ZaynulAbadinTuhin. Considering the time of date they are posting, that's unlikely. ;) — Thom A, Aug 22 '19 at 08:25

Thom A · Accepted Answer · 2019-08-22T08:30:09.973

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);

SQL Unable to Insert Converted DateTime in Column

1 Answers1