I have some XML in a column in a database. One element has a ful ISO date
like 2015-11-21T01:51:17+00:00. I wrote a SELECT statement that converts
it to a DATE with XML table like so:


SELECT ... DATE(resp.PICK_DATE) AS PICK_DATE ... FROM ...
XMLTABLE (
'$response/root/lineitem' passing XML_RESPONSE as "response"
COLUMNS
...
PICK_DATE CHAR(10)PATH 'PICK_DATE',
...

However, If I wrap that inside a SQL function and execute it I get SQLSTATE
22001. Variable not compatible or value too long.

After pulling my hair out and combing through the joblog I realize the
answer is DATE(SUBSTR(resp.PICK_DATE, 1, 10)) AS PICK_DATE. My question is,
is there some way to make the function auto truncate like the adhov SQL
does?

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].