1. Problem
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.
http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc00801.1540/doc/html/san1281565081725...
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
