Sybase IQ | Error date time failed

[MariaD]

Hi folks,

We have a view in Sybase IQ. If we applied a filter on a datetime variable using SQL pass-through, works fine. But if we define a libname and execute a data step (view example below), the following error appear:

data test;

  set libSyb.Vw_table;

  where DtVariable = '30SEP2020:00:00:00'dt;

run;

ERROR: Object not found in database. Error Code: -21 SQL Anywhere Error -1009145: Data type conversion is not possible. Conversion of '30SEP2020 00:00:00.000' to data type date time failed. --(dflib/dfe_HDBValue.cxx 468)

We verified the DtVariable definition, using PROC CONTENTS, and it appears as datetime. 

Any idea?

Regards, 

[ChrisNZ]

It looks like SAS creates an invalid Sybase query.

Use option sastrace to see what query is sent to Sybase.

It seems that SAS sends '30SEP2020 00:00:00.000'when according to this page it should send something like DATETIME ('MM/DD/YY hh:nn:sssssss').

What's the syntax used in the pass-through query?

[MariaD]

Hi @ChrisNZ ,

Follows the results using sastrace option for data step process:

ACCESS ENGINE:  Exiting dbrqsub with SQL Statement set to 585 1606855166 no_name 0 DATASTEP

SELECT <list of columns>

WHERE  (DtProcessamento = '30SEP2020 00:00:00.000' ) 586 1606855166 no_name 0 DATASTEP

The SQL pass-through sentences used and returned ok is:

proc sql;
  connect to sybaseiq as conn (host='' server='' db='' user='' password='');
  create table test as 
  select * from connection to conn
  (select top 10 *
   from database.view
   where Variable='20200930');
quit;

Regards,

[ChrisNZ]

proc sql;
  connect to sybaseiq as conn (host='' server='' db='' user='' password='');
  create table test as 
  select * from connection to conn
  (select top 10 *
   from database.view
   where Variable='20200930');
quit;

Did you replace  DtProcessamento    with   Variable   above?

Is it a string? Is it a timestamp?

Anyway, the issue seems to be that SAS is confused and generates invalid Sybase code.

You need to contact SAS Tech Support and they'll help you.

[MariaD]

Hi @ChrisNZ ,

Yes, "Variable" is DtProcessamento and it's defined as timestamp. I'm already opened a ticket with SAS Support. 

Thanks.

[SASKiwi]

If its a Sybase IQ timestamp this should work:

proc sql;
  connect to sybaseiq as conn (host='' server='' db='' user='' password='');
  create table test as 
  select * from connection to conn
  (select top 10 *
   from database.view
   where Variable='2020-09-30');
quit;

[ChrisNZ]

@SASKiwi Explicit pass-through works. It's implicit pass-through that's broken as SAS seems to write an invalid-syntax clause.

[SASKiwi]

@ChrisNZ - I blame Friday-itis 🙂

[AlexandreG · Accepted solution]

Tech Support found that the library was being assigned using the SYBASE engine instead of SYBASEIQ.

After correcting the engine, the query was also executed correctly through DATA Step.

@ChrisNZ was correct, SAS was indeed sending the incorrect syntax to the database, but only because the incorrect Sybase engine was used to assign the library. 🙂

[MariaD]

That's correct. Thanks!