Re: time format error

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)
.

Relevant Pages

  • time format error
    ... but when run through QueryAnalyzer or EnterpriseManager doesn't ... Here are 2 examples of the query: ... System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '12'. ...
    (comp.databases.ms-sqlserver)
  • Re: Which query is running
    ... GetAddress is the name of a function which combines and formats the address ... Each query could contain more than 1 output field that need parameters to be ... This is part of a Club database. ... On Error GoTo GetParameters_Err ...
    (comp.databases.ms-access)
  • Re: Parameter Query and Date calculations....
    ... the CVDATE() didn't make any difference to the records that were returned. ... append query so the calculated date is added to a field in a table with the ... I shall certainly be more careful with formats in the ... >> I am calculating a future date using the DateAdd function in a query (the ...
    (comp.databases.ms-access)
  • Re: Multiple date formats in a Table
    ... Athough you suggest doing the conversion of my original ... the query on that field... ... historical research, I located a reference for one of my ... Multiple date formats in a Table ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Can I output query result to text files in QueryAnalyzer?
    ... You can send the output in file in different formats. ... In Query Analyzer goto Tools-->Options and then click Results tab and under ... save the output in a file save with .csv extension. ... Open the file in Excel. ...
    (microsoft.public.sqlserver.programming)