View Full Version : A problem with dates

05-13-2007, 06:54 PM
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…


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)
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.

05-15-2007, 06:06 PM

This may also be due to date setting of your machine on which SQL Server is installed. It take date as MM/DD/YYYY format. If you are searching with a format of 22/05/2007 it checks that 22 is not valid month and returns error.

05-16-2007, 07:09 PM
why don't you try using query parameters instead? declare a parameter with the date type and put your value in it, the conversion, if the format is valid, should be made automatically.

05-16-2007, 07:54 PM
strSQL2 = "Update TestTBL SET DateStatus = '" & DateClosedString & "' WHERE EndDate >= '" & Today & "'"

It can't compare a date with a string.

05-16-2007, 08:37 PM
strSQL = "Update TestTBL SET DateStatus =@strDateClosed WHERE EndDate >= @DateToday"

cmd.commandtext = strSQL
Dim param As New SqlParameter("@strDateclosed", Data.SqlDbType.Type)
param.value = txtTest.text
param = new SqlParameter("@DateToday", Data.SqlDbType.Datetime)
param.value = Date.Today()

this could be better, but I'm in a hurry. It should work, though. The Type in italics is the type of that on your database, as I wasnt sure if it was a string or a date.

05-27-2007, 06:12 PM
It looks to me that your database has been set up to use "American" date formats. I don't know how to fix this as a server default or fix it in the database itself but there is a simple code workaround.

After opening the database connection, but before executing any other command, execute the following command:

set dateformat dmy

It will remain active for all other commands executed against the same connection.

On a related subject, you may have similar problems with DateValidator controls. Just add the following line to your web.config:

<globalization requestEncoding="utf-8" responseEncoding="utf-8" culture="en-GB" uiCulture="en-GB" />

in the system.web section.