Posted by Hugo Kornelis on 12/20/05 23:32

On 20 Dec 2005 06:38:15 -0800, Johnny Ruin wrote:

>What do you think of a query that generates a
>System.Data.SqlClient.SqlException when submitted via a application,
>but when run through QueryAnalyzer or EnterpriseManager doesn't
>generate a error? Here are 2 examples of the query:
>
>SELECT table1.*,table2.field1,table2.field2,table2.field3 FROM
>table1,table2 WHERE table1.field1 = table2.field1 AND
>table1.field2='103' AND
>table2.TStamp > '12/19/2005 12:20:14 PM' ORDER BY table2.TStamp
>
>System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '12'.
>
>SELECT table1.*,table2.field1,table2.field2,table2.field3 FROM
>table1,table2 WHERE table1.field1 = table2.field1 AND
>table1.field2='103' AND
>table2.TStamp > '12/8/2005 8:29:43 AM'ORDER BY table2.TStamp
>
>System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '8'.
>
>I believe the error is referencing the hour portion of the timestamp.
>When I run these queries through QA/EM I don't get a result set ... so
>maybe those apps just trap the exception and ignore it. Still, see
>anything wrong with the TStamp?

Hi Johnny,

In addition to the replies Dan and Mike posted, let me add that you
should also stop using ambiguous date formats. The following three
formats are the only three formats GUARANTEED to be unambiguous:

* yyyymmdd - note: no dashes, slashes, dots, or other punctuation.
* yyyy-mm-ddThh:mm:ss - note: date compontents seperated by dashes, time
components seperated by colons, time in 24-hour clock and an uppercase T
in the middle.
* yyyy-mm-ddThh:mm:ss.mmm - same as above, but with milliseconds added
(seperated from seconds by a decimal point).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Back to original message]