|
Robert Ingleby noticed a problem in this code that generates a SQL SELECT statement.
cmd = _
"SELECT * FROM tData WHERE StationID LIKE '*BOOM*'" & _
" AND UserID = '" & cmbUserID & "'" & _
" AND DTG > #" & strStartDate & "#" & _
" AND DTG < #" & _
Format(DateAdd("d", 1, strEndDate), "short date") & "#" & _
"ORDER BY DTG;"
|
Robert said:
If the dates are 19/08/2002 and 22/08/2002, respectively, it all works fine (returns the correct six records). Yet, if the dates are 19/08/2002 and 1/09/2002, it returns no records.
The problem is SQL statements must use the mm/dd/yyyy date format even if your operating system is set to display dates in the dd/mm/yyyy format, as Robert's is. I'm not sure what values SQL is using for these dates and it would be much easier to find this sort of bug if the system raised an error when it sees the month numbers 19 and 22. In any case, changing the date format fixed the problem.
For more information on database programming in VB .NET, see my book Visual Basic .NET Database Programming.
Bill Roberts had this follow-up:
VB uses two different date locales - the system locale that determines the output format of the date, and the code locale, which is ALWAYS U.S. English. Using the "#" delimiter (as recommended by MSDN) forces the date to be interpreted as the code locale - U.S. English, and thus mm/dd/yyyy; the system locale has no influence on this.
However, VB tries to be helpful when interpreting an entered date, and this can cause no end of trouble and confusion. For example:
#18/04/2002# is interpreted as 18th April 2002
#04/18/2002# is also interpreted as 18th April 2002
#08/04/2002# is interpreted as 4th August 2002
It seems that the date is taken as mm/dd/yyyy when it makes sense, otherwise VB assumes that it's dd/mm/yyyy, and does it's own format conversion behind the
scenes. This problem is compounded when dates are passed to SQL Server as part of an embedded SQL statement.
It is far, far better to ignore the MSDN advice, and pass dates as SQL character strings, always pre-formatted to the desired date format. For example:
"Where startdate > '" & format$(Now, "mm/dd/yyyy") & "' "
Note that the date string MUST be enclosed in single quotes, as above.
This can still cause problems, though, depending on the date format that your particular SQL Server installation is expecting. One solution is to include a "SET DATEFORMAT mdy" statement at the start of the Select statement. A better solution is to always format the date in a way that is not open to misinterpretation by SQL Server; dates such as 2002-08-04 and 04 Aug 2002 seem to be translated OK irrespective of the date format settings in either VB or SQL Server.
So, the above "Where" statement could be coded as either:
"Where startdate > '" & format$(Now, "yyyy-mm-dd") & "' "
or
"Where startdate > '" & format$(Now, "dd mmm yyyy") & "' "
Both of these will work as expected.
As an aside, I don't think that it's a good idea to use "short date" and "long date" as part of a format$ function, where the formatted dates are being used as part of a SQL statement, as these formats can be defined differently on different machines. Thus, a piece of code might work fine on machine A, but either crashes or produces incorrect results on machine B.
Stefan De Prins recommends something similar. He says:
Default SQL Server uses mm/dd/yyyy, but that's not the case everywhere. I found that the only way to be completely sure about your dates is to use the following format : YYYY-MM-DD (ISO Standard). This way your date always gets interpreted correctly, no matter what the regional settings on the system(s) are, or what the date format in SQL Server is.
I've also gotten consistent results by including the TO_DATE function in SQL statements.
"WHERE StartDate >
TO_DATE('2002-11-01 00:00:00','YYYY-MM-DD HH24:MI:SS')"
This tells SQL exactly what format the date uses.
| 
