locked
PLEASE HELP ME: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM. RRS feed

  • Question

  • User1777427112 posted

    I cannot for the life of me figure out why im still getting this error. I have tried everything i can think of from using dbnull.value to setDOBnull() to datetime.minvalue. Can anyone help me?

     

    Here's the code on my BLL:

        Public Function AddEmployee(ByVal SSN As String, ByVal FirstName As String, ByVal LastName As String, ByVal PreferredName As String, ByVal MaidenName As String, ByVal MaritalStatus As Nullable(Of Char), ByVal DOB As Nullable(Of Date), ByVal Gender As Nullable(Of Char), ByVal Ethnicity As String, ByVal StartDate As Nullable(Of Date), ByVal TerminationDate As Nullable(Of Date)) As Boolean

            ' Create a new employeeRow instance
            Dim employees As New HRMS.EmployeesDataTable()
            Dim employee As HRMS.EmployeesRow = employees.NewEmployeesRow()
            Dim sqldatenull As SqlDateTime
            sqldatenull = SqlDateTime.MinValue.Value

            employee.SSN = SSN
            employee.FirstName = FirstName
            employee.LastName = LastName


            If PreferredName Is Nothing Then employee.SetPreferredNameNull() Else employee.PreferredName = PreferredName
            If MaidenName Is Nothing Then employee.SetMaidenNameNull() Else employee.MaidenName = MaidenName
            If Not MaritalStatus.HasValue Then employee.SetMaritalStatusNull() Else employee.MaritalStatus = MaritalStatus.Value
            If Not DOB.HasValue Then employee.SetDOBNull() Else employee.DOB = DOB.Value
            If Not Gender.HasValue Then employee.SetGenderNull() Else employee.Gender = Gender.Value
            If Ethnicity Is Nothing Then employee.SetEthnicityNull() Else employee.Ethnicity = Ethnicity
            If StartDate = DateTime.MinValue Then employee.StartDate = SqlDateTime.MinValue.Value Else employee.StartDate = StartDate.Value
            'If StartDate = DateTime.MinValue Then employee.SetDOBNull() Else employee.StartDate = StartDate.Value
            'If Not StartDate.HasValue Then employee.StartDate = sqldatenull Else employee.StartDate = StartDate.Value
            If TerminationDate = DateTime.MinValue Then employee.TerminationDate = SqlDateTime.MinValue.Value Else employee.TerminationDate = TerminationDate.Value P

    Wednesday, February 21, 2007 1:22 PM

Answers

  • User-1614457691 posted

    where in my code do i insert 1973?

    There are many ways to do it. 

    A simple way is to call a helper function to fix each date before using it.

    So, before you use myDate1 just do something like this where you "fix" the date before you use it to make sure it is within bounds...

    myDate1 = DateUtility.FixDate(myDate1)

    ...and then use myDate1 to insert.

    The code for a sample DateUtility is below.

    Note that you can refactor the FixDate() method to do whatever you want, based on your system requirements. For example, the sample code below sets a date that is out of bounds to be DefaultDateTimeNull. However, it could be that you want to change the code so that dates that are greater-than max should be set to DefaultDateTimeMax and dates that less-than min are set to DefaultDateTimeMin. And so on. The code below is just a sample of one simple way to do it.

    Here is the sample code... 

     


    Imports Microsoft.VisualBasic

    Public NotInheritable Class DateUtility

     ''' <summary>
     ''' This is the custom max-date value.
     ''' </summary>
     Public Const DefaultDateTimeMax = #12/30/9999#

     ''' <summary>
     ''' This is the custom min-date value.
     ''' </summary>
     Public Const DefaultDateTimeMin = #1/2/1753#

     ''' <summary>
     ''' This is the custom null date value.
     ''' </summary>
     Public Const DefaultDateTimeNull = #1/1/1753#

     ''' <summary>
     ''' This will fix the given date, if necessary.
     ''' </summary>
     ''' <param name="dateTimeValue">This is the date to fix.</param>
     ''' <returns>A date that is within max/min bounds.</returns>
     ''' <remarks>
     ''' Note that DateTime.CompareTo returns...
     ''' Less than zero - This instance is less than value.
     ''' Zero - This instance is equal to value.
     ''' Greater than zero - This instance is greater than value.
     '''
     ''' Note that SQL Server 2000 datetime holds...
     ''' Date and time data from January 1, 1753, through December 31, 9999, with
     ''' an accuracy of three-hundredths of a second, or 3.33 milliseconds.
     ''' </remarks>
     Public Shared Function FixDate(ByVal dateTimeValue As DateTime) As DateTime
      Dim myDateTimeFixed = DateUtility.DefaultDateTimeNull

      Try
       Dim myCompareResultForMin As Integer = Integer.MinValue

       'Check the min.
       myCompareResultForMin = dateTimeValue.CompareTo(DateUtility.DefaultDateTimeMin)

       If (myCompareResultForMin < 0) Then
        'The date given is < min, so use null.
        myDateTimeFixed = DateUtility.DefaultDateTimeNull
       Else
        'The date given is >= min. Now, check the max.
        Dim myCompareResultForMax As Integer = Integer.MinValue
        myCompareResultForMax = dateTimeValue.CompareTo(DateUtility.DefaultDateTimeMax)

        If (myCompareResultForMax <= 0) Then
         'The date given is OK <= max, so use it.
         myDateTimeFixed = dateTimeValue
        Else
         'The date given is > max, so use null.
         myDateTimeFixed = DateUtility.DefaultDateTimeNull
        End If
       End If
      Catch
       myDateTimeFixed = DateUtility.DefaultDateTimeNull
      End Try

      Return dateTimeValue
     End Function

    End Class
     

     

    HTH.

    Thank you.

    -- Mark Kamoski

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 22, 2007 12:36 PM

All replies

  • User-1614457691 posted

    I cannot for the life of me figure out why im still getting this error. I have tried everything i can think of from using dbnull.value to setDOBnull() to datetime.minvalue. Can anyone help me?

     

    Here's the code on my BLL:

        Public Function AddEmployee(ByVal SSN As String, ByVal FirstName As String, ByVal LastName As String, ByVal PreferredName As String, ByVal MaidenName As String, ByVal MaritalStatus As Nullable(Of Char), ByVal DOB As Nullable(Of Date), ByVal Gender As Nullable(Of Char), ByVal Ethnicity As String, ByVal StartDate As Nullable(Of Date), ByVal TerminationDate As Nullable(Of Date)) As Boolean

            ' Create a new employeeRow instance
            Dim employees As New HRMS.EmployeesDataTable()
            Dim employee As HRMS.EmployeesRow = employees.NewEmployeesRow()
            Dim sqldatenull As SqlDateTime
            sqldatenull = SqlDateTime.MinValue.Value

            employee.SSN = SSN
            employee.FirstName = FirstName
            employee.LastName = LastName


            If PreferredName Is Nothing Then employee.SetPreferredNameNull() Else employee.PreferredName = PreferredName
            If MaidenName Is Nothing Then employee.SetMaidenNameNull() Else employee.MaidenName = MaidenName
            If Not MaritalStatus.HasValue Then employee.SetMaritalStatusNull() Else employee.MaritalStatus = MaritalStatus.Value
            If Not DOB.HasValue Then employee.SetDOBNull() Else employee.DOB = DOB.Value
            If Not Gender.HasValue Then employee.SetGenderNull() Else employee.Gender = Gender.Value
            If Ethnicity Is Nothing Then employee.SetEthnicityNull() Else employee.Ethnicity = Ethnicity
            If StartDate = DateTime.MinValue Then employee.StartDate = SqlDateTime.MinValue.Value Else employee.StartDate = StartDate.Value
            'If StartDate = DateTime.MinValue Then employee.SetDOBNull() Else employee.StartDate = StartDate.Value
            'If Not StartDate.HasValue Then employee.StartDate = sqldatenull Else employee.StartDate = StartDate.Value
            If TerminationDate = DateTime.MinValue Then employee.TerminationDate = SqlDateTime.MinValue.Value Else employee.TerminationDate = TerminationDate.Value P

    Use 1/1/1753 as a default system value understood to be "null" and insert that.

     

    Wednesday, February 21, 2007 2:32 PM
  • User1777427112 posted

     where in my code do i insert 1973?

    I cannot for the life of me figure out why im still getting this error. I have tried everything i can think of from using dbnull.value to setDOBnull() to datetime.minvalue. Can anyone help me?

     

    Here's the code on my BLL:

        Public Function AddEmployee(ByVal SSN As String, ByVal FirstName As String, ByVal LastName As String, ByVal PreferredName As String, ByVal MaidenName As String, ByVal MaritalStatus As Nullable(Of Char), ByVal DOB As Nullable(Of Date), ByVal Gender As Nullable(Of Char), ByVal Ethnicity As String, ByVal StartDate As Nullable(Of Date), ByVal TerminationDate As Nullable(Of Date)) As Boolean

            ' Create a new employeeRow instance
            Dim employees As New HRMS.EmployeesDataTable()
            Dim employee As HRMS.EmployeesRow = employees.NewEmployeesRow()
            Dim sqldatenull As SqlDateTime
            sqldatenull = SqlDateTime.MinValue.Value

            employee.SSN = SSN
            employee.FirstName = FirstName
            employee.LastName = LastName


            If PreferredName Is Nothing Then employee.SetPreferredNameNull() Else employee.PreferredName = PreferredName
            If MaidenName Is Nothing Then employee.SetMaidenNameNull() Else employee.MaidenName = MaidenName
            If Not MaritalStatus.HasValue Then employee.SetMaritalStatusNull() Else employee.MaritalStatus = MaritalStatus.Value
            If Not DOB.HasValue Then employee.SetDOBNull() Else employee.DOB = DOB.Value
            If Not Gender.HasValue Then employee.SetGenderNull() Else employee.Gender = Gender.Value
            If Ethnicity Is Nothing Then employee.SetEthnicityNull() Else employee.Ethnicity = Ethnicity
            If StartDate = DateTime.MinValue Then employee.StartDate = SqlDateTime.MinValue.Value Else employee.StartDate = StartDate.Value
            'If StartDate = DateTime.MinValue Then employee.SetDOBNull() Else employee.StartDate = StartDate.Value
            'If Not StartDate.HasValue Then employee.StartDate = sqldatenull Else employee.StartDate = StartDate.Value
            If TerminationDate = DateTime.MinValue Then employee.TerminationDate = SqlDateTime.MinValue.Value Else employee.TerminationDate = TerminationDate.Value P

    Use 1/1/1753 as a default system value understood to be "null" and insert that.

     

    Wednesday, February 21, 2007 2:59 PM
  • User1777427112 posted
    please help
    Wednesday, February 21, 2007 6:33 PM
  • User-1614457691 posted

    where in my code do i insert 1973?

    There are many ways to do it. 

    A simple way is to call a helper function to fix each date before using it.

    So, before you use myDate1 just do something like this where you "fix" the date before you use it to make sure it is within bounds...

    myDate1 = DateUtility.FixDate(myDate1)

    ...and then use myDate1 to insert.

    The code for a sample DateUtility is below.

    Note that you can refactor the FixDate() method to do whatever you want, based on your system requirements. For example, the sample code below sets a date that is out of bounds to be DefaultDateTimeNull. However, it could be that you want to change the code so that dates that are greater-than max should be set to DefaultDateTimeMax and dates that less-than min are set to DefaultDateTimeMin. And so on. The code below is just a sample of one simple way to do it.

    Here is the sample code... 

     


    Imports Microsoft.VisualBasic

    Public NotInheritable Class DateUtility

     ''' <summary>
     ''' This is the custom max-date value.
     ''' </summary>
     Public Const DefaultDateTimeMax = #12/30/9999#

     ''' <summary>
     ''' This is the custom min-date value.
     ''' </summary>
     Public Const DefaultDateTimeMin = #1/2/1753#

     ''' <summary>
     ''' This is the custom null date value.
     ''' </summary>
     Public Const DefaultDateTimeNull = #1/1/1753#

     ''' <summary>
     ''' This will fix the given date, if necessary.
     ''' </summary>
     ''' <param name="dateTimeValue">This is the date to fix.</param>
     ''' <returns>A date that is within max/min bounds.</returns>
     ''' <remarks>
     ''' Note that DateTime.CompareTo returns...
     ''' Less than zero - This instance is less than value.
     ''' Zero - This instance is equal to value.
     ''' Greater than zero - This instance is greater than value.
     '''
     ''' Note that SQL Server 2000 datetime holds...
     ''' Date and time data from January 1, 1753, through December 31, 9999, with
     ''' an accuracy of three-hundredths of a second, or 3.33 milliseconds.
     ''' </remarks>
     Public Shared Function FixDate(ByVal dateTimeValue As DateTime) As DateTime
      Dim myDateTimeFixed = DateUtility.DefaultDateTimeNull

      Try
       Dim myCompareResultForMin As Integer = Integer.MinValue

       'Check the min.
       myCompareResultForMin = dateTimeValue.CompareTo(DateUtility.DefaultDateTimeMin)

       If (myCompareResultForMin < 0) Then
        'The date given is < min, so use null.
        myDateTimeFixed = DateUtility.DefaultDateTimeNull
       Else
        'The date given is >= min. Now, check the max.
        Dim myCompareResultForMax As Integer = Integer.MinValue
        myCompareResultForMax = dateTimeValue.CompareTo(DateUtility.DefaultDateTimeMax)

        If (myCompareResultForMax <= 0) Then
         'The date given is OK <= max, so use it.
         myDateTimeFixed = dateTimeValue
        Else
         'The date given is > max, so use null.
         myDateTimeFixed = DateUtility.DefaultDateTimeNull
        End If
       End If
      Catch
       myDateTimeFixed = DateUtility.DefaultDateTimeNull
      End Try

      Return dateTimeValue
     End Function

    End Class
     

     

    HTH.

    Thank you.

    -- Mark Kamoski

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, February 22, 2007 12:36 PM
  • User1777427112 posted
    Thanks Mkamoski!! [:)]
    Friday, March 30, 2007 6:01 PM
  • User974841433 posted

    Mark, I really cannot thank you enough!!!

    I think the final line from the FixDate function should be "Return myDateTimeFixed" instead of "Return dateTimeValue (which is passed by value)"

    I'm sleepy and could be mistaken.

     

    Thanks,

    Nubs

    Monday, March 31, 2008 4:56 PM
  • User-1614457691 posted

    I think the final line from the FixDate function should be "Return myDateTimeFixed" instead of "Return dateTimeValue (which is passed by value)"

    You are right. I am wrong. That is a bug in my code above.

    Here is the corrected version.

     

    Imports Microsoft.VisualBasic

    Public NotInheritable Class DateUtility

     

     ''' <summary>
     ''' This is the custom max-date value.
     ''' </summary>
     Public Const DefaultDateTimeMax = #12/30/9999#

     

     ''' <summary>
     ''' This is the custom min-date value.
     ''' </summary>
     Public Const DefaultDateTimeMin = #1/2/1753#

     

     ''' <summary>
     ''' This is the custom null date value.
     ''' </summary>
     Public Const DefaultDateTimeNull = #1/1/1753#

     ''' <summary>
     ''' This will fix the given date, if necessary.
     ''' </summary>
     ''' <param name="dateTimeValue">This is the date to fix.</param>
     ''' <returns>A date that is within max/min bounds.</returns>
     ''' <remarks>
     ''' Note that DateTime.CompareTo returns...
     ''' Less than zero - This instance is less than value.
     ''' Zero - This instance is equal to value.
     ''' Greater than zero - This instance is greater than value.
     '''
     ''' Note that SQL Server 2000 datetime holds...
     ''' Date and time data from January 1, 1753, through December 31, 9999, with
     ''' an accuracy of three-hundredths of a second, or 3.33 milliseconds.
     ''' </remarks>
     Public Shared Function FixDate(ByVal dateTimeValue As DateTime) As DateTime
      Dim myDateTimeFixed = DateUtility.DefaultDateTimeNull

      Try
       Dim myCompareResultForMin As Integer = Integer.MinValue

       'Check the min.
       myCompareResultForMin = dateTimeValue.CompareTo(DateUtility.DefaultDateTimeMin)
     

       If (myCompareResultForMin < 0) Then
        'The date given is < min, so use null.
        myDateTimeFixed = DateUtility.DefaultDateTimeNull
       Else
        'The date given is >= min. Now, check the max.
        Dim myCompareResultForMax As Integer = Integer.MinValue
        myCompareResultForMax = dateTimeValue.CompareTo(DateUtility.DefaultDateTimeMax)

        If (myCompareResultForMax <= 0) Then
         'The date given is OK <= max, so use it.
         myDateTimeFixed = dateTimeValue
        Else
         'The date given is > max, so use null.
         myDateTimeFixed = DateUtility.DefaultDateTimeNull
        End If
       End If
      Catch
       myDateTimeFixed = DateUtility.DefaultDateTimeNull
      End Try

      Return myDateTimeFixed
     End Function

    End Class

     

    Thursday, April 3, 2008 8:18 AM
  • User1525227164 posted

    Thank you. I don't really know what your code is doing, but without it, my stored procedure would not work because of the overflow issue. Now it works fine. Really. Thanks.

    Wednesday, April 20, 2011 3:00 AM