471,090 Members | 1,388 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,090 software developers and data experts.

time format error

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?

Dec 20 '05 #1
4 4076
> 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?
I think these are not the actual queries sent to SQL Server. You can verify
this with a Profiler trace. You will get these errors if you remove the
quotes around the datetime:

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

Also, this problem suggests that you are building a string within your
application and then executing it. You might instead consider using a
parameterized query and command parameters. This can improve performance,
security and provide cleaner code. Also, you won't need to worry about
datetime format issues or embedded quotes.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Johnny Ruin" <sc**********@gmail.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com... 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?

Dec 20 '05 #2
There is no space between the time and ORDER BY

'12/8/2005 8:29:43 AM'ORDER BY table2.TStamp

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mi**@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"Johnny Ruin" <sc**********@gmail.com> wrote in message
news:11*********************@z14g2000cwz.googlegro ups.com...
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?

Dec 20 '05 #3
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)
Dec 20 '05 #4
Hello everyone,

Thank you for your responses. My apologies for such a late reply.

Dan, you were right. Those weren't the queries that were being sent to
the server. I put the profiler to work and found the queries that I'm
being sent had a '#' wrapping the date. I had known about that issue
and put a line in to replace the #'s with quotes. I had misplaced my
pstrQuery->Replace call so that my logfile showed the single quotes
even though the # were being sent in .... so silly. Sorry I
bothered the group with it.

Mike, that missing space issue was my typo in presenting the question.
I was editing the original query so things would be closer to the
simplest case.

Hugo, thanks for that info. I found some stuff about that in the
"Writing International Transact-SQL Statements". Unfortunately this
string is the same string the user sees. I'm basically a pass
through, so it'd be special code to snag it and change. Seems to be
working, but if I have more date issues I'll revisit that element.

Thanks again for everyone's comments, and again, apologies for late
reply and foolish post. It just never pays to be in hurry does it?

Dec 29 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Kenneth | last post: by
4 posts views Thread by Christaaay | last post: by
2 posts views Thread by DWalker | last post: by
7 posts views Thread by Jerome | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.