Dates and SQL SELECT

[otherside]

Hey guys, i just found out something that i've been looking for a long time, it actually caused me some problems and that's how i found out.

 

Have you ever tried to select with SQL a date (ShortDate Datafield)?

 

say like :

 

SELECT * FROM Table WHERE PurDate = 30/1/2000

 

first of all from i what i found out the correct way and only way that works has to be like that :

 

SELECT * FROM Table WHERE PurDate = #30/1/2000#

 

but that's not enough ..

let's say that as a normal person you'd store the date in a date datafield as DD/MM/YYYY pay attention on the DD/MM.

 

well ... when you enter #30/1/2000# on the SQL it get's it as

1/30/2000, meaning that 30 is the month, 1 is the day.

 

so to make it work correctly you have to enter it as:

 

SELECT * FROM Table WHERE PurDate = #1/30/2000#

 

SELECT * FROM Table WHERE PurDate = #MONTH/DAY/YEAR#

 

and yes in my regional settings the short date is DAY/MONTH/YEAR.

 

Just some info guys for future reference

also if you have any input please feel free to reply.

[Derek Stone]

Use a long date and avoid the problem altogether.

Dim dt As DateTime = DateTime.Now
MessageBox.Show(dt.ToString("MMMM dd, yyyy"), String.Empty)

Edited July 9, 2003 by Derek Stone