Date format: SP and direct SELECT return different values

In each of two LUW databases created for the territory “UK” (one using DB2 11.1.2 (express-c) and the other on DB2 10.5.9 (Express ed.)), I am faced with a curiosity. The date format of each database was set to ISO using the following instruction:

$ db2 bind @db2ubind.lst datetime ISO blocking all grant public
Now, when I “select someDateColumn from theTable”, I get “2017-10-17”, which is the date that was saved in the column (Oct 17, 2017) and is of the desired format. However, using a stored procedure produces “10/17/2017” which appears to be in some other date format than ISO. We want the data in ISO format. Below is my screen output:

$ db2 “select theDate from foo”

$ db2 “create or replace procedure foo() dynamic result sets 1 begin declare c cursor with return to client for select theDate from foo; open c;end”
$ db2 “call foo()”
Result set 1
What am I doing wrong? Or, how does one ensure that a stored procedure returns dates in the date format that is configured in the database?

Thank you.

PS: I noticed the issue because Javascript would produce “Invalid date” from parsing loans data fetched by db2 SPs, each time the procedures returned result sets containing dates such as 2017-25-10.


Leave a Reply