out-of-range datetime value

[NinaWilliam]

Hi there

I wrote a web application and it was working fine with no errors in my local server and the live server. My system lost its data and the hard drive got corrupted. So my hard drive was replaced with a new one. When I configure the web application on my new hard drive and run it, it was working ok except for the part that is has date selection on it I get the following error

Exception Type: System.Data.SqlClient.SqlException
Exception Message: The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Exception Source: .Net SqlClient Data Provider
Exception Target Site: Read

I didn’t change the code or anything; the same version is working on the live server with no issues. I don’t know why it is giving me this error in my machine.

Any ideas??

 

[NinaWilliam]

Help.........

 

[kulrom]

This error implies that you are trying to convert/cast a char datatype to datatime.

1st, you should be certain that the column (you fetch data from) of your table has datatype DATETIME.

2nd, you can try with using a parametrized query/sp (this may eliminate your problem)

3rd, change your new regional settings in case if they are different than the ones you had previously. e.g. if your previous regional settings were supporting mm/dd/yyyy date format and then the new support dd/mm/yyyy.

4rt, post the code (portion that fetch the data with the datetime column).

 

[NinaWilliam]

Hi kulrom and thank you for your reply...

I have tried all 3 points you said... and it didn't solve the problem...
Here is the code:

Dim ddate As Date
        Dim SelDate As Date
        Dim date1 As Date

        ddate = Format(Now(), "MMM/dd/yyyy")
        SelDate = Format(Me.Calendar1.SelectedDate, "MMM/dd/yyyy")

 	If SelDate = Date.MinValue Then

            date1 = ddate

        Else

            date1 = SelDate

        End If

       'Declaring sql connection and sql command
        Dim SqlComAppClerk As New SqlClient.SqlCommand("select AppointDate from Appointments where Appointments.AppointDate = CONVERT(DATETIME, '" & date1 & "', 102)", SqlConnAppClerk)

        Dim SqlDA As New SqlDataAdapter(SqlComAppClerk)
        Dim ds As New DataSet


        'Open sql connection if it is closed
        If SqlConnAppClerk.State <> ConnectionState.Open Then

            SqlConnAppClerk.Open()

        End If

        SqlDA.Fill(ds) ''<<<< here the error Occurs

 

[kulrom]

It must be something wrong with the record(s) you are trying to retrieve. It works just fine for me.

        Dim ddate As DateTime
        Dim SelDate As DateTime
        Dim date1 As DateTime

        ddate = Format(Now(), "MMM/dd/yyyy")
        SelDate = Format(Me.Calendar1.SelectedDate, "MMM/dd/yyyy")

        If SelDate = Date.MinValue Then
            date1 = ddate
        Else
            date1 = SelDate
        End If

        'Declaring sql connection and sql command
        Dim SqlComAppClerk As New SqlCommand( _
        "SELECT AppointDate FROM Appointments WHERE AppointDate = @mydate", _
        SqlConnAppClerk)
        SqlComAppClerk.Parameters.Add("@mydate", SqlDbType.DateTime).Value = date1


        SqlConnAppClerk.Open()
        Dim table As New DataTable
        table.Load(SqlComAppClerk.ExecuteReader(CommandBehavior.CloseConnection))
        SqlConnAppClerk.Close()

        ' test it 
        For Each row As DataRow In table.Rows
            Response.Write(row(0).ToString & "<br />")
        Next

Copy/paste this code in your app and test it. If it does not work then you should check the DB. It is either, culture settings as mentioned or this column/field is not datetime.

 

[NinaWilliam]

Hi guys ^_______^

I was able to fix the problem by replacing 102 to 103 in the following code line...

SELECT Appointments.AppointDate WHERE (Appointments.AppointDate = CONVERT(DATETIME, '" & date1 & "', 103))

and it works fine now ^___^

Happy Programming

 

[kulrom]

The issue is obvious now. You had the date entries in a different format than 101 which is USA Format (Standard) mm/dd/yy and your current culture settings are probably British or French as 103 converts the date to dd/mm/yy.

Next time just post an entry sample to get a better help