[RESOLVED] Puzzled By Date

[Shaggy Hiker]

I put together this code as a test from a project I am working on:

 Dim startDate As String = Date.MinValue.ToShortDateString
 Dim endDate As String = "1/1/2025"
 Using cn As New SqlClient.SqlConnection(mConString)
     Using cmd As SqlClient.SqlCommand = cn.CreateCommand
         Using da As New SqlClient.SqlDataAdapter(cmd)
             Try
                 cn.Open()
                 cmd.Transaction = cn.BeginTransaction
                 If endDate = "" Then
                     cmd.CommandText = "DELETE FROM Incubation WHERE [IncubationDate]>= '" & startDate & "'"
                 Else
                     cmd.CommandText = "DELETE FROM Incubation WHERE [IncubationDate]>= '" & startDate & "' AND [IncubationDate]<= '" & endDate & "'"
                 End If
                 cmd.ExecuteNonQuery()
                 cmd.Transaction.Rollback()
             Catch ex As Exception
                 If cmd.Transaction IsNot Nothing Then
                     cmd.Transaction.Rollback()
                 End If

             End Try
         End Using
     End Using
 End Using

Much of this is irrelevant, as it is vestigial remnants from the actual project. The key is that query. I concatenate in a local variable. If that local variable is something like '1/1/2024', then all is well. If the local variable (startDate) is set as shown, then I get an exception stating "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."

So, perhaps SQL Server doesn't like Date.MinValue. That's fine, because I could easily choose some other value that would work just as well. What has me really baffled, though, is that this works just fine in a different method which addresses a different table.

The table that this is failing on, has no data in it. Nor did the table this worked on, when it worked. The field was originally called just plain Date, which I thought was a bad idea, so I changed it to IncubationDate. That shouldn't matter, though, because it appears to all come down to startDate. If that is Date.MinValue, it fails here, while if it is some other date, it works here.

What is going on?

[jmcilhinney]

From the documentation here, the range for the 'datetime' data type is "1753-01-01 (January 1, 1753) through 9999-12-31 (December 31, 9999)", so Date.MinValue shouldn't work. You need to use 'datetime2' for 1/01/0001 to be a valid value.

[Shaggy Hiker]

Sure, I assumed that the issue had to do with the range being different in different contexts. The solution is also simple, since I really don't need to use an absurdly broad date range.

What had me baffled is that the exact code works in one function in the app, but not in a different function in the same app. In both cases, startdate is appended as shown, into a SQL query string. The only difference between the two is the table that is being queried. One works, the other does not. In fact, there are now five such queries, each targeting a different table. Four of the five work fine (and three have worked fine for years), but now one does not.

As I wrote that, I realized that the issue had to be in the DB fields themselves, and that was the case. For reasons unknown to me, the people supplying the data decided to supply datetime for the misbehaving case, but date only for the rest. There is no good reason for this, since a quick glance at the data (only a quick glance, since there are many hundreds of thousands of records) shows that the times included are total BS. For an action that can't reasonably happen early in the morning, and never happens after dark, they are all 7 or 8 AM, except for one that is at 2300.

So, they changed the data type to include crap along with meaning. I missed that.

[jmcilhinney]

Unless you used a parameter that was specifically set to use datetime, I would assume that the database engine is inferring those values to be datetime2, in order for the value to make sense. It probably then converts the existing datetime values in the database to datetime2 for comparison. A query could work with those implicit conversions but trying to save data could not, because the final data type has to be datetime if that's what the target column is set to.