Skip to Content

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

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply