0

Please have a look at the database structure below:

CREATE TABLE TestStartDate (PersonID int, StartDate datetime NOT NULL)
INSERT INTO TestStartDate VALUES(1,'1899-12-30')

and the code below:

Private Sub Form1_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        Dim objCommand As SqlCommand, objCommand2 As SqlCommand
        Dim objCon As SqlConnection
        Try
            objCommand = New SqlCommand
            Using objCommand
                Dim strConString As String = "Data Source=IANSCOMPUTER;Initial Catalog=Test;Integrated Security=True;MultipleActiveResultSets=true"
                objCon = New SqlConnection
                Using(objCon)
                    objCon.ConnectionString = strConString
                    objCon.Open()
                    objCommand.Connection = objCon
                    objCommand.CommandText = "select startdate from TestStartDate "
                    Dim objDR As SqlDataReader = objCommand.ExecuteReader
                    If objDR.HasRows Then
                        objDR.Read()
                        Using objCon
                            objCommand2 = New SqlCommand
                            Using objCommand2
                                objCommand2.Connection = objCon
                                objCommand2.CommandText = "INSERT INTO TestStartDate (StartDate) VALUES (@StartDate)"
                                objCommand2.Parameters.AddWithValue("@StartDate", DateValue(objDR("StartDate"))) 'line 24
                                'objCommand2.Parameters.AddWithValue("@StartDate", objDR("StartDate")) 'line 25
                                objCommand2.ExecuteNonQuery() 'Line 26
                                Dim date1 As Date = objDR("startdate")
                            End Using
                        End Using
                    End If
                End Using
            End Using
        Catch ex As Exception
            Throw
        Finally

        End Try
    End Sub

The code throws an exception on line 26 (line 26 is annotated):

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM

I can resolve this by commenting out line 24 and uncommenting line 25. Why does DATEVALUE cause this exception?

I have looked at the following webpage: http://msdn.microsoft.com/en-us/library/6d6k22a5%28v=vs.80%29.aspx. This suggests that DATEVALUE might be converting 30/12/1899 to 01/01/01 (01/01/01 is not an acceptable value in SQL Server)? If this is the case, then why is 30/12/1899 converted to 01/01/01?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
w0051977
  • 15,099
  • 32
  • 152
  • 329
  • Two possibilities: The date time value yielded by `objDR` is out of range, or it becomes invalid during the conversion by `DateValue`. What value does `DateValue(objDR("StartDate"))` yield? Have you tried replacing the `DateValue` function by writing, `objDR.GetDateTime(objDR.GetOrdinal("StartDate"))`? – stakx - no longer contributing Mar 24 '13 at 13:06
  • @stakx, I have edited the question, please have another look. – w0051977 Mar 24 '13 at 13:12
  • Check this link for some of the history with the '1899 date feature': http://stackoverflow.com/questions/3963617/why-is-1899-12-30-the-zero-date-in-access-sql-server-instead-of-12-31 – Pieter Geerkens Mar 24 '13 at 13:20
  • You have formatted your date as YYYY/MM/DD where as the SQL expects it to be DD/MM/YYYY – Zeddy Mar 24 '13 at 13:43
  • Are you trying to use 1899-12-30 or 1900-01-01 as some type of "magic" value? Why not just use NULL instead? – Aaron Bertrand Mar 24 '13 at 14:29

0 Answers0