472,981 Members | 1,102 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,981 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 4139
> 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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Kenneth | last post by:
Hello, I'm having some serious problems debugging a script that I'm trying to make work. I'm working on a form where a user can type in a time (in the format of HH:MM), and another script...
4
by: Christaaay | last post by:
I have been using the code below successfully for almost a year. yesterday, I began getting a run time error 6 (overflow). I am using the code in an Access 2000 database. Can anyone help me...
3
by: Reney | last post by:
I am using Access Database in my program. The column in the table that I am going to use has date/time value with Medium Time selected. (HH:mm). The program is recording a clock in time to this...
15
by: Khurram | last post by:
I have a problem while inserting time value in the datetime Field. I want to Insert only time value in this format (08:15:39) into the SQL Date time Field. I tried to many ways, I can extract...
2
by: DWalker | last post by:
In Visual Studio (Visual Basic) .NET 2002, I noticed that this: Dim Elapsed as DateTime = Now - Now gives a "compile time" error (error in the IDE), saying that the '-' operator is not...
7
by: Jerome | last post by:
Hallo, I know a lot has already been told about date/time fields in a database but still confuses me, specif when dealing with SQLserver(Express). It seems that sqlserver only accepts the date in...
5
by: Summu82 | last post by:
HI I have to convert a time in the format i have year month day hour min and seconds I need to convert this into time in seconds since 1 jan 1970 i.e the
5
by: rouven | last post by:
Hi, i am trying to convert that time format '05:26:40 Jun 19, 2007 PDT' into mysql compatible format like YYYY-MM-DD HH:MM:SS. the code i tried was: from datetime import datetime from time...
3
by: DontB3 | last post by:
Hi, I'm new in this forum, and i hope someone can help. I'm creating an automatic application that transfer a database from Access -> DBF -> Oracle. When My App try to execute Insert SQL...
5
by: Ledo | last post by:
I have a form that I want to show invoices in a date range. When clicking on the button I get the following error: Run Time Error 3075 Syntax error (missing operator) in query expression...
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
2
by: DJRhino | last post by:
Was curious if anyone else was having this same issue or not.... I was just Up/Down graded to windows 11 and now my access combo boxes are not acting right. With win 10 I could start typing...
0
by: Aliciasmith | last post by:
In an age dominated by smartphones, having a mobile app for your business is no longer an option; it's a necessity. Whether you're a startup or an established enterprise, finding the right mobile app...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
4
NeoPa
by: NeoPa | last post by:
Hello everyone. I find myself stuck trying to find the VBA way to get Access to create a PDF of the currently-selected (and open) object (Form or Report). I know it can be done by selecting :...
3
NeoPa
by: NeoPa | last post by:
Introduction For this article I'll be using a very simple database which has Form (clsForm) & Report (clsReport) classes that simply handle making the calling Form invisible until the Form, or all...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
4
by: GKJR | last post by:
Does anyone have a recommendation to build a standalone application to replace an Access database? I have my bookkeeping software I developed in Access that I would like to make available to other...

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.