A problem with dates

[SarH]

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.

[TheCPUWizard]

A string version of a date, HAS to bee in the correct format. Much better to use a strongly typed SQLDate.

[SarH]

Could it be that today is in this format (13/05/2007) and my enddate in the database is something like this(12/05/2007 00:00:00).

How do I go about a strongly typed SQLDate?

[Quell]

u could just format the DateTime string when u create the object, and then just compare the stuff.
take a look here:
http://msdn2.microsoft.com/en-us/library/97x6twsz.aspx
It has some stuff on how to format DateTime.

[TheCPUWizard]

Use the types here (http://msdn2.microsoft.com/en-us/library/system.data.sqltypes.aspx) when dealing with SQL. You will sace yourself hours or more of grief.

[SarH]

I've managed to get the formats matching in the database its (12/05/2007 00:00:00) and in the string its (13/05/2007 19:57:21). But I get the error message "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."

here's the code...
Private Sub TestDate()
Dim DateClosedString As String = "Closed"
Dim cnn As Data.SqlClient.SqlConnection
Dim cmd As Data.SqlClient.SqlCommand
Dim TodayTest As String = DateTime.Now
Dim strSQL2 As String

strSQL2 = "Update TestTBL SET DateStatus = '" & DateClosedString & "' WHERE EndDate >= '" & TodayTest & "'"
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()

So if the formats are ok, I cant figure out whats wrong. Is it the SQL statement thats the problem?

[unspoken_words2003]

The date format ur using is
month/day/year..

what ur trying is 13/xx/xxxx....
the 13 month is not possible...

thts why it shows the error.

Regards,
sy

[mcmcom]

and in addition you should use SQLParameters instead of putting variables in your SQL String.

hth,
mcm

[SarH]

How do I do a sqlparameter? is it just bad code passing variables to a string, like i'm using?

[mcmcom]

yes it is bad code.

read this article. http://www.startvbdotnet.com/ado/sqlparameters.aspx

hth,
mcm

[SarH]

The article is quite interesting, thanks. I'm going to try and look up some standards for code asp.net code on google now, as I think I'm picking up some nasty habbits. Best stop them whilst I'm still a newbie.

[mcmcom]

also always use Try / Catch Blocks and the "using" statement when connecting to a Database Server.

hth,
mcm