DateTime mapping format error

[Fish-Grape]

I have a SQL filtered by DateTime type,eg: DECLARE @vConfirmTime AS datetime = @ConfirmTime; SELECT * FROM dbo.[Table] B (NOLOCK) WHERE ConfirmTime > @vConfirmTime ORDER BY ConfirmTime;

And I set param @vConfirmTime = '10/25/2023 4:38:35 PM',eg: NewConnection().QuerySqlAsync(Sql, new { @ConfirmTime = "10/25/2023 4:38:35 PM" }

However,when it excuted in SQL Server,the SQL became this format: DECLARE @vConfirmTime AS DATETIME = N'25/10/2023 4:38:35 PM'; SELECT * FROM dbo.[Table] B (NOLOCK) WHERE ConfirmTime > @vConfirmTime ORDER BY ConfirmTime;

Such error format(N'25/10/2023 4:38:35 PM') caused error:The conversion of a nvarchar data type to a datetime data type resulted in an out-of-range value.

[stale[bot]]

This issue has been automatically marked as stale because it has not had recent activity. It will be closed if no further activity occurs. Thank you for your contributions.

[jonwagner]

I added this test case and cannot reproduce:

                [Test]
        public void DateTimeShouldSerializeProperlyToTextQueries()
        {
            var s = "10/25/2023 4:38:35 PM";
            var sql = "DECLARE @vConfirmTime AS datetime = @ConfirmTime; SELECT @vConfirmTime";
            var p = new { @ConfirmTime = s };

            var result = Connection().QuerySql<DateTime>(sql, p).FirstOrDefault();
            ClassicAssert.AreEqual(DateTime.Parse(s), result);
        }

Since this is executing raw sql, Insight will pass the parameter value without conversion. If there is a datetime conversion issue, it's likely on the server side.

Please provide a broken test case and we'll take another look at it.