Problem with dates

[hobbes2103]

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!

[Nerseus]

You want to us Month/Day/Year syntax. So change the SQL to:

Dim mySelect As String = "SELECT name, age FROM MyTable WHERE birthdate >'12/31/1980'" 

 

-Nerseus

[hobbes2103]

It doesn't 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)

[karimgarza]

Localized date

 

try the following

 

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

[karimgarza]

link for countries

 

here is the link for each country

 

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemglobalizationcultureinfoclasstopic.asp