ODBC Timestamp - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

ODBC Timestamp

Author

Topic

nukeawhale
Starting Member

14 Posts

Posted - 2006-03-21 : 13:48:05

I'm importing data via an ODBC connection.

I am importing using a query. I want to constrain the query by the previous day from a date field (such as GETDATE() - 1).

However, according to the documentation (http://msdn2.microsoft.com/en-us/library/ms190234(SQL.90).aspx), I have found that the ODBC requires a timestamp such as {ts '2006-01-01 12:00:00'} to constrain the query.

Is there anyway I can get this to be constrained by doing something similar to GETDATE() - 1 to pull in the previous day's data? I don't really want to re-import all the data from table each day.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-21 : 14:33:14

There is no reason why you can't use a SQL Server function in a query that you pass via ODBC.


where
			-- GE Start of Yesterday
	MyDateCol >= dateadd(dd,datediff(dd,0,getdate())-1,0)	and
			-- LT Start of Today
	MyDateCol <  dateadd(dd,datediff(dd,0,getdate()),0)

CODO ERGO SUM

nukeawhale
Starting Member

14 Posts

Posted - 2006-03-21 : 14:40:19

I am using an AR System ODBC connection. When I use anything except the "ts" defined in the Microsoft ODBC documentation (linked above), I get an error stating "Expected lexical element not found: <identifier>".

I assume the identifier it is refering to is the "ts".

I tried your statement and received the error. Here is a sample of the query I am using in the connection.

select "AR_Table1"."Record_ID",
"AR_Table1p"."Status",
"AR_Table1"."Create_Date",
from "AR_Table1"
where "AR_Table1"."Create_Date" > {ts '2006-01-01 12:00:00'}

Note that all tables need to be identified within quotations or the SQL does not parse.

Your thoughts?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-21 : 14:51:53

It is hard to say what the problem is, since you didn't post the code that gave you the error.

CODO ERGO SUM

nukeawhale
Starting Member

14 Posts

Posted - 2006-03-21 : 15:00:24

The code you provided is what gives the error.

Here it is:

select "AR_Table1"."Record_ID",
"AR_Table1p"."Status",
"AR_Table1"."Create_Date",
from "AR_Table1"
where "AR_Table1"."Create_Date" > {dateadd(dd,datediff(dd,0,getdate()),0)}

If I removed the brackets from the "dateadd" statement I get an error stating "Unexpected End of SQL Statement"; as the ODBC documentation shows that datetime constraint is within brackets.

Not sure how to insert attachments here, but I can post screenshots if available.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-21 : 17:08:40

That doesn't seem to be the code I supplied; it is certainly not valid to enclose the functions in {}, and I notice that you did not use the date range the way I showed.

I just noticed that your query seems invalid another way. The comma after "AR_Table1"."Create_Date" should not be there.


select "AR_Table1"."Record_ID", 
"AR_Table1p"."Status", 
"AR_Table1"."Create_Date",  <--- No comma here
from "AR_Table1" 
where "AR_Table1"."Create_Date" > {dateadd(dd,datediff(dd,0,getdate()),0)}

Have you tried just getting your SQL code to execute in Query Analyzer first, before executing it from whatever client you are using? You can test it with the SET QUOTED_IDENTIFIER ON option to make it work the same.

CODO ERGO SUM

nukeawhale
Starting Member

14 Posts

Posted - 2006-03-22 : 08:28:11

You are correct, the comma shouldn't be there - I truncated my original statement and did not get rid of the comma.

Also, for ODBC imports, brackets have to be used for timestamps - see the documentation link above. If I don't use them, the SQL can't be parsed in the DTS package. I get an unexpected End of SQL Statement error or unexpected extra token - I believe it is looking for the bracket.

The following statement parses correctly in Query Analyzer, but does not parse in the DTS package:

select "AR_Table1"."Record_ID",
"AR_Table1p"."Status",
"AR_Table1"."Create_Date"
from "AR_Table1"
where "AR_Table1"."Create_Date" > dateadd(dd,datediff(dd,0,getdate()),0)

The DTS package provides this error:

"Unexpected extra token: ("

I can not execute the code in Query Analyzer because the table is not in a SQL Server - it is in an AR System (which is Oracle based I think). Unless you know how to specify the ODBC connection data in Query Analyzer so it can execute?

Anyways, it is not until I put in the brackets does parse correctly in the DTS:

select "AR_Table1"."Record_ID",
"AR_Table1p"."Status",
"AR_Table1"."Create_Date"
from "AR_Table1"
where "AR_Table1"."Create_Date" > {ts '2006-01-01 12:00:00'}

Is there anyway to upload screenshots here? It may be helpful to see the error.

Edit - I uploaded images of the error and parse, linked below. Note the table name is different as I didn't want to write out the long table name in the forum.

Error: http://nukeawhale.home.mindspring.com/error.bmp

Parsed: http://nukeawhale.home.mindspring.com/parsed.bmp

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-22 : 10:56:40

"...which is Oracle based I think..."

Nice that you finally mentioned that!

This site is meant for questions about Microsoft SQL Server, so you may want to post your questions at dbforumns or another web site that handles questions about Oracle SQL.

CODO ERGO SUM

nukeawhale
Starting Member

14 Posts

Posted - 2006-03-22 : 11:01:35

Yes, but I am using a SQL Server DTS package to import into SQL Server.

And Microsoft's MSDN page provides documentation on how to do this, just not how to specify anything other than a specific date.

Since I am using SQL Server as the client and server software, and an ODBC connection that is used by SQL Server, I was under the assumption that a SQL Server forum would be appropriate.

I guess I should add the syntax in the DTS package isn't Oracle based - it is the syntax required by SQL Server - making more of a SQL issue than an Oracle issue. ODBC is there to allow foreign data into SQL - if this forum only deals with import/export from SQL Servers, then I admit I should not have posted here.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-03-22 : 11:16:44

No, my point is that you are writing a query against an Oracle database, so if there is any function or whatever for doing what you want to do, it will be an Oracle query and has to conform to the rules for Oracle queries.

If you want to figure out how to correctly format the ODBC timestamp in VB script inside the DTS package, you can also do that.

CODO ERGO SUM

nukeawhale
Starting Member

14 Posts

Posted - 2006-03-22 : 11:28:34

I don't want to over-complicate this matter with a VB script; I was just looking for a quick fix here.

And I'm not totally convinced this has to conform to Oracle rules.

The ODBC timestamp documentation states:

The ODBC API defines escape sequences to represent date and time values, which ODBC calls timestamp data. This ODBC timestamp format is also supported by the OLE DB language definition (DBGUID-SQL) supported by the Microsoft OLE DB Provider for SQL Server. Applications using the ADO, OLE DB, and ODBC-based APIs can use this ODBC timestamp format to represent dates and times.

This leads me to believe that this timestamp requirement is there regardless of the data source (Oracle, etc.). It seems like it is more of an ODBC issue, and SQL does not allow much wiggle room with the syntax.

Your thoughts?

da808wiz
Starting Member

1 Post

Posted - 2010-05-07 : 16:39:54

I know this thread is old, but just thought I'd note some of my experience which I think will help.

Whenever you're in AR System BMC Remedy and querying via AR System ODBC Driver, it gives some pretty weird errors such as the "Expected Lexical Element Not Found" whenever it encounters an error in an expression, maybe more specifically dealing with dates.

I had tried to use where Reported_Date between CDate(#05/01/2010#) and CDate(#05/07/2010#) which produced the confusing lexical error.

By changing this to where Reported_Date>=CDate(#05/01/2010#) and Reported_Date<=CDate(#05/07/2010#) the error went away.

Another quirk was when trying to use a function and give it an alias which matches the field name used in the function or selected field list, I got a "Circular Reference caused by (fieldname)" error. To fix this, I went from:

select iif(assignee is null,'(not assigned)',assignee) as Assignee ... from HPD_Help_Desk

To:
select iif(a.assignee is null,'(not assigned)',a.assignee) as Assignee ... from HPD_Help_Desk as a

I hope these tips help...

Dabbling in lots of things, latest is Silverlight...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources