Dates and SQL SELECT

[otherside]

Hey guys, i just found out something that ive been looking for a long time, it actually caused me some problems and thats 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 thats not enough ..
lets say that as a normal person youd 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 gets 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)