Problem with dates

H

hobbes2103

Active member
Joined
Jul 10, 2003
Messages
43
I have this code that selects people who are born after 31/12/1980 :

Dim MyCon As New OleDb.OleDbConnection
Dim myReader As OleDb.OleDbDataReader
Dim myConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source= " & Application.StartupPath & "\" & "mydatabase.mdb;"
MyCon = New OleDb.OleDbConnection(myConnectionString)
MyCon.Open()
Dim myCommand As New OleDb.OleDbCommand

Dim mySelect As String = "SELECT name, age FROM MyTable WHERE birthdate >31/12/1980"
myCommand = New OleDb.OleDbCommand(mySelect, MyCon)

The problem comes from the selection by date because if I select by name or by age, it works.
How do i have to write the 31/12/1980 so that there is no "unhandled exception"?

Thank you!
 
You want to us Month/Day/Year syntax. So change the SQL to:
Code: 
Dim mySelect As String = "SELECT name, age FROM MyTable WHERE birthdate >12/31/1980"

-Nerseus
 
It doesnt work either.... (and in the french version of VB .net it is Day/month/year syntax)...
I think there it is a problem with the (theremust be a # somewhere)
 
Localized date

try the following

Code: 
Dim sometime As System.DateTime
us english
Dim us As New System.Globalization.CultureInfo("en-US")
spanish from mexico
Dim mx As New System.Globalization.CultureInfo("es-MX")
Dim strDate As String
strDate = "31/12/1980"
sometime = CType(strDate, Date)
-----> for the US
Dim mySelect As String = "SELECT name, age FROM MyTable WHERE birthdate > #" + sometime.ToString("d", us) + "#"
myCommand = New OleDb.OleDbCommand(mySelect, MyCon)
------> for MExico
Dim mySelect As String = "SELECT name, age FROM MyTable WHERE birthdate > #" + sometime.ToString("d", mx) + "#"
myCommand = New OleDb.OleDbCommand(mySelect, MyCon)

This solution is a little bit better because you are actually localizing the date formats. The really best solution would be (and I am talking about SQL server 2000 I do not know about other DBMS) to create a store procedure and give it the the localized date as date type.

Best of Luck
 
Back
Top