Question about Sybase ODBC connection using SQL

Reply
Occasional Contributor
Posts: 6

Question about Sybase ODBC connection using SQL

Hi,

I have a perplexing question.....I am connecting to a Sybase table using ODBC through SQL.  The connection works fine...I am able to retrieve records from the table.

However, I need to restrict those records to certain date ranges.  For instance all create dates after 01Jan2012.  I have tried everything but cannot get this to work.   When I open up the unfiltered table that retrieved using SQL, the field imported using SAS is a  I get a date in DATE9. format, such as 27FEB2006.  But I read in documentation that Sybase stores the dates as a timestamp format as 20060227.

I am so confused.  I am wondering if I can use SAS functions such as the input function in my SQL code that connects to Sybase.  Furthermore, I am wondering if I can even use a Date Literal like "26FEB2006"D in my SQL code at all.   I honestly just don't know what I am doing here.  Any help or if you could illuminate me based on your own experiences I would be most appreciative.

Thanks...Dave

proc sql;

     connect to ODBC (uid=xxxx pwd=xxxx dsn=xxxx);

     create table tempdata as

     select * from connection to odbc

          (select create_date

               from mytable

               where input(create_date,DDMMYYYY.)="27FEB2006"D)

     ;

     disconnect from odbc;

run;

Super User
Posts: 17,868

Re: Question about Sybase ODBC connection using SQL

The method your using is called SQL pass through and that means you need to use the Sybase functions to filter your date.

If you set up your connection outside the query using a libname statement for example, you could then use standard sas functions.

Super Contributor
Posts: 578

Re: Question about Sybase ODBC connection using SQL

Just a quick point...

Setting up a library to access other database platforms works...but...If you use SAS intrinsic functions that do not translate into that platform's language, SAS will do all of the processing locally.  So..you'll still get an answer...but the performance might be awful if you have joins across large tables. I think SAS date functions are not usually passed.

Super User
Posts: 17,868

Re: Question about Sybase ODBC connection using SQL

Depends on what 'date' functions, but I think date literals, which seems to be the issue here, would be passed. I don't see anything in the documentation to clarify that point.

SAS/ACCESS(R) 9.2 for Relational Databases: Reference, Fourth Edition

Super User
Posts: 17,868

Re: Question about Sybase ODBC connection using SQL

Dates in sybase can be

  • date
  • time
  • smalldatetime
  • datetime

You need to figure out which yours is and then use the appropriate type.

See here:


SyBooks Online Page 3

 

Occasional Contributor
Posts: 6

Re: Question about Sybase ODBC connection using SQL

Thank you all for enlightening me!  I feel very dumb....I understand now that I was using the pass-through process and that the functions etc that I was passing to Sybase were not intinsic Sybase functions.  And that is why I was getting this error.  This turned out to be incredibly easy.  If I just add a clause onto the where statement like:

Create_date >= "2013/02/01"

then SQL returns exactly what I need.  I was making this way too hard, not surprising since I didn't understand the mechanism behind how SAS and Sybase were communicating.  Still not an expert by any means, but I think I get why it is working now and not before.  Thanks so much for all your help!!!

Super User
Posts: 5,257

Re: Question about Sybase ODBC connection using SQL

If you are just sub setting according to a data constant, there is no need for writing explicit SQL pass-thru, the where clause will be passed to Sybase when using implicit pass-thru - and the syntax is simpler (and RDBMS independent).

Data never sleeps
Ask a Question
Discussion stats
  • 6 replies
  • 565 views
  • 0 likes
  • 4 in conversation