[Conversion Error] Failed to execute a select query with cast function via IQ Client.

1. Problem

[Conversion Error] Failed to execute a select query

IQ failed to execute a select query with cast function via dbisql/dbiqlc.

But It’s working well via isql.

 

-> select CAST (’04/09/2015′ AS datetime) AS Converted_Date from iq_dummy;

 

[Error]

    Cannot convert ‘04/09/2015‘ to a timestamp

    SQLCODE=-157, ODBC 3 State=”07006″

 

2. Cause

 

As far as you know the isql is oriented for ASE not IQ.

So The default value of “date order” is different from IQ.

The default order of date is ‘YMD’ in IQ.

** Test in IQ vs ASE

– create table a_tab(col1 datetime)

– insert into a_tab values(getdate())

– select * from a_tab

1) [ASE]

col1

——————————-

Apr 9 2015 8:30AM

2) [IQ]

col1

‘2015-04-09 08:54:13.155′

## Related Option

[DATE_ORDER Option]

: Controls the interpretation of date formats.

  Default

  ‘YMD’. This corresponds to ISO date format specifications.

https://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc00801.1540/doc/html/san1281565081725.html?resultof=%22%64%61%74%65%5f%66%6f%72%6d%61%74%22%20

3. Solutions/Workarounds

 

Need to change the query with date_order option as follows.

– set temporary option date_order=’MDY’;
– select CAST (’04/09/2015′ AS datetime) AS Converted_Date from iq_dummy;

===

HTH

Gi-Sung Jang