Date problem

  • Thread starter Thread starter acko bogicevic
  • Start date Start date
A

acko bogicevic

Hi everybody
On page i have text box txtDate. User writes date in dd.mm.yyyy format.

When the button btnSearch is clicked i have to query sqlserver 2000
database. Here is a code:

Private Sub btnSearch_Click(ByVal sender As Object, ByVal e As
System.EventArgs) Handles btnSearch.Click

...
If Trim(txtDate.Text) <> "" Then
Dim dCreatedFrom As Date
Try
dCreatedFrom = CDate(Trim(txtDate.Text))
Catch ex As Exception
Response.Redirect("GlobalError.aspx")
End Try

strSQL =" Select * from Orders Where OrderDate >= '" &
Format(dCreatedFrom, "YYYY.MM.DD") & "'"
End If
End Sub

When i return dataset with this strSQL i always get the error:
Syntax error converting datetime from character string
How can i solve this
Thanks
Alex
 
Output the SQL string and try it in SQL Server. Try YYYYMMDD that is AFAIK
the SQL Server ISO format.

Patrice
 
Alex,

Why not let .Net do this for you?

Declare a date parameter for your SQL statement:

Dim SqlCommand As New SqlClient.SqlCommand

SqlCommand.CommandType = CommandType.Text

SqlCommand.CommandText = " SELECT * FROM Orders WHERE OrderDate >=
@dCreatedFrom"

SqlCommand.Parameters.Add(New SqlClient.SqlParameter("@dCreatedFrom",
SqlDbType.DateTime))

SqlCommand.Parameters.Item("@dCreatedFrom").Value = dCreatedFrom

The SqlCommand object will automatically do the date creation for you based
on your original date object. It automatically finds the parameter and
replaces it with your value via the "@" symbol.


--
Sincerely,

S. Justin Gengo, MCP
Web Developer / Programmer

Free code library at:
www.aboutfortunate.com

"Out of chaos comes order."
Nietzche
 
Back
Top