A problem with dates.

[CaJack]

Hi,
I keep getting an error when I run my code. Here’s the error “The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated”. It points to this part of my code…

 cmd.ExecuteNonQuery() 

Here’s my code…

    Private Sub TestDate()
        Dim DateClosedString As String = "Closed"
        Dim cnn As Data.SqlClient.SqlConnection
        Dim cmd As Data.SqlClient.SqlCommand
        Dim TodayTest As Date = Today
        Dim strSQL2 As String
 
        strSQL2 = "Update TestTBL SET DateStatus = '" & DateClosedString & "' WHERE EndDate >= '" & Today & "'"
        Dim conn As String
        conn = "Data Source=.\SQLEXPRESS;AttachDbFilename=""|DataDirectory|\aspnetdb.mdf"";Integrated Security=True;User Instance=True"
        cnn = New Data.SqlClient.SqlConnection(conn)
        cmd = New Data.SqlClient.SqlCommand(strSQL2, cnn)
        cnn.Open()
        cmd.ExecuteNonQuery()
        cnn.Close()
End Sub 

What I want it to do is check my TestTBL and if any EndDates are greater or equal to todays date, write closed to the DateStatus field.
The dates are saved to the database as DateTime. This is copy and passed from an enddate field in my database “22/05/2007 00:00:00”. I’m not really need the time part but I cant find any other way.
Can anyone help? I’ve been pulling my hair out over this one for days.

[vanc]

I think your sql needs some characters like "# date #" in access database.

hope this help.

[george1106]

Hi! try inserting a string with the next format:

year/month/day hh:mm:ss

something like String.Format("{0}/{1}/{2} {3}:{4}", date.Year,date.Month,date.Day,date.Hour,date.Minut e)

to give to the DB engine a common string wich is accepted at least in SQLServe, Postgres and Oracle

I hope it helps :D

[CaJack]

Hi! try inserting a string with the next format:

year/month/day hh:mm:ss

something like String.Format("{0}/{1}/{2} {3}:{4}", date.Year,date.Month,date.Day,date.Hour,date.Minut e)

to give to the DB engine a common string wich is accepted at least in SQLServe, Postgres and Oracle

I hope it helps :D

Yep sorted it, thank you.