topic Re: Sybase IQ | Error date time failed DATETIME ('MM/DD/YY hh:nn:sssssss') in SAS Programming

Sybase IQ | Error date time failed

MariaD — Tue, 01 Dec 2020 13:31:05 GMT

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,

Re: Sybase IQ | Error date time failed DATETIME ('MM/DD/YY hh:nn:sssssss')

ChrisNZ — Tue, 01 Dec 2020 20:04:23 GMT

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?

Re: Sybase IQ | Error date time failed DATETIME ('MM/DD/YY hh:nn:sssssss')

MariaD — Wed, 02 Dec 2020 12:15:50 GMT

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,

Re: Sybase IQ | Error date time failed DATETIME ('MM/DD/YY hh:nn:sssssss')

ChrisNZ — Wed, 02 Dec 2020 21:55:43 GMT

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.

Re: Sybase IQ | Error date time failed DATETIME ('MM/DD/YY hh:nn:sssssss')

MariaD — Thu, 03 Dec 2020 12:36:54 GMT

Hi @ChrisNZ ,

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

Thanks.

Re: Sybase IQ | Error date time failed DATETIME ('MM/DD/YY hh:nn:sssssss')

SASKiwi — Thu, 03 Dec 2020 20:42:49 GMT

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;

Re: Sybase IQ | Error date time failed DATETIME ('MM/DD/YY hh:nn:sssssss')

ChrisNZ — Thu, 03 Dec 2020 22:43:35 GMT

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

Re: Sybase IQ | Error date time failed DATETIME ('MM/DD/YY hh:nn:sssssss')

SASKiwi — Thu, 03 Dec 2020 23:30:53 GMT

@ChrisNZ - I blame Friday-itis 🙂

Re: Sybase IQ | Error date time failed DATETIME ('MM/DD/YY hh:nn:sssssss')

AlexandreG — Fri, 04 Dec 2020 15:46:31 GMT

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. 🙂

Re: Sybase IQ | Error date time failed DATETIME ('MM/DD/YY hh:nn:sssssss')

MariaD — Fri, 04 Dec 2020 15:52:11 GMT

That's correct. Thanks!