On 22-Aug-2016 13:31 -0700, Bruno Almeida wrote:

I tried to change the insert date format in my DB2 10.1, but the
behavior is the same:

$ db2 bind @db2ubind.lst datetime USA blocking all grant public

<<SNIP>>

Any ideas?

None other than possibly the following ideas that I suppose could assist:

1> exiting and restarting the CLP; though likely already done, with
no improvement.?

2> following the instructions from the following link, beyond just
issuing the BIND; i.e. preceding that action, with the cd [change
directory] action noted: [https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.7.0/com.ibm.db2.luw.admin.nls.doc/doc/r0004572.html]
Paraphrasing that doc for USA [originally ISO in place of USA seen here]:
"…
You can use the command line to change the default date format to USA (MM/DD/YYYY) by doing the following:

1. Change your current directory to sqllib\bnd

• For Windows operating systems, change the directory to
c:\program files\IBM\sqllib\bnd

• For UNIX operating systems, change the directory to /home/db2inst1/sqllib/bnd

2. Connect to the database from the operating system shell using the
SYSADM authority:

db2 connect to DBNAME
db2 bind @db2ubind.lst datetime USA blocking all grant public

where DBNAME is the database name and USA is the new date format.
…"

FWiW: I do not have an installation of DB2 LUW with which to try
anything, so I am unable to do much more than direct anyone to
documentation. Though given the OP had noted clearly that 10.1 was
being used, I at least should have offered links to the docs from that
release; sorry, I just happened to have the 9.7 docs open, and so that
was just for my convenience.

--
Regards, Chuck

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Friday, August 19, 2016 at 6:53:35 PM UTC-3, Bruno Almeida wrote:

Hello friends,

DB2 10.1 LUW

I'm trying to understand this scenario:

$ db2 "create table TEST ( C1 date) "
DB20000I The SQL command completed successfully.

$ db2 "values current date"

1
----------
08/19/2016

1 record(s) selected.

$ db2 "insert into TEST values current date"
DB20000I The SQL command completed successfully.

$ db2 "select * from TEST"

C1
----------
08/19/2016

1 record(s) selected.

Hummm, OK!

Now look ...

$ db2 "insert into TEST values ('08/20/2016')"
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0181N The string representation of a datetime value is out of range. SQLSTATE=22007

$ db2 "insert into TEST values ('20/08/2016')"
DB20000I The SQL command completed successfully.

$ db2 "select * from TEST"

C1
----------
08/19/2016
08/20/2016

2 record(s) selected.

db2inst1@renata-molinaro:~$ db2 get db cfg | grep -i territory
Database territory = US

Would you help me with this issue?

Regards,
Bruno.

Hi Chuck,

I tried to change the insert date format in my DB2 10.1, but the behavior is the same:

$ db2 bind @db2ubind.lst datetime USA blocking all grant public

LINE MESSAGES FOR db2ubind.lst
------ --------------------------------------------------------------------
SQL0061W The binder is in progress.

LINE MESSAGES FOR db2clpnc.bnd
------ --------------------------------------------------------------------
SQL0595W Isolation level "NC" has been escalated to "UR".
SQLSTATE=01526

LINE MESSAGES FOR db2arxnc.bnd
------ --------------------------------------------------------------------
SQL0595W Isolation level "NC" has been escalated to "UR".
SQLSTATE=01526

LINE MESSAGES FOR db2ats_sps.bnd
------ --------------------------------------------------------------------
1173 SQL0204N "SYSTOOLS.ADMINTASKS" is an undefined name.
SQLSTATE=01532
1203 SQL0204N "SYSTOOLS.ADMINTASKS" is an undefined name.
SQLSTATE=01532
1234 SQL0204N "SYSTOOLS.ADMINTASKS" is an undefined name.
SQLSTATE=01532
1482 SQL0204N "SYSTOOLS.ADMINTASKS" is an undefined name.
SQLSTATE=01532
1499 SQL0204N "SYSTOOLS.ADMINTASKS" is an undefined name.
SQLSTATE=01532
1517 SQL0204N "SYSTOOLS.ADMINTASKS" is an undefined name.
SQLSTATE=01532
1555 SQL0204N "SYSTOOLS.ADMINTASKS" is an undefined name.
SQLSTATE=01532
1679 SQL0204N "SYSTOOLS.ADMINTASKS" is an undefined name.
SQLSTATE=01532
1696 SQL0204N "SYSTOOLS.ADMINTASKS" is an undefined name.
SQLSTATE=01532
1715 SQL0204N "SYSTOOLS.ADMINTASKS" is an undefined name.
SQLSTATE=01532
1732 SQL0204N "SYSTOOLS.ADMINTASKS" is an undefined name.
SQLSTATE=01532
1895 SQL0204N "SYSTOOLS.ADMINTASKSTATUS" is an undefined name.
SQLSTATE=01532
1950 SQL0204N "SYSTOOLS.ADMINTASKSTATUS" is an undefined name.
SQLSTATE=01532
1962 SQL0204N "SYSTOOLS.ADMINTASKS" is an undefined name.
SQLSTATE=01532
1979 SQL0204N "SYSTOOLS.ADMINTASKSTATUS" is an undefined name.
SQLSTATE=01532

LINE MESSAGES FOR db2_adminotm.bnd
------ --------------------------------------------------------------------
346 SQL0204N "SYSTOOLS.ADMIN_MOVE_TABLE" is an undefined name.
SQLSTATE=01532
375 SQL0204N "SYSTOOLS.ADMIN_MOVE_TABLE" is an undefined name.
SQLSTATE=01532
459 SQL0204N "SYSTOOLS.ADMIN_MOVE_TABLE" is an undefined name.
SQLSTATE=01532
540 SQL0204N "SYSTOOLS.ADMIN_MOVE_TABLE" is an undefined name.
SQLSTATE=01532
574 SQL0204N "SYSTOOLS.ADMIN_MOVE_TABLE" is an undefined name.
SQLSTATE=01532
594 SQL0204N "SYSTOOLS.ADMIN_MOVE_TABLE" is an undefined name.
SQLSTATE=01532
675 SQL0204N "SYSTOOLS.ADMIN_MOVE_TABLE" is an undefined name.
SQLSTATE=01532
771 SQL0204N "SYSTOOLS.ADMIN_MOVE_TABLE" is an undefined name.
SQLSTATE=01532
908 SQL0204N "SYSTOOLS.ADMIN_MOVE_TABLE" is an undefined name.
SQLSTATE=01532

LINE MESSAGES FOR db2ubind.lst
------ --------------------------------------------------------------------
SQL0091N Binding was ended with "0" errors and "26"
warnings.

$ db2 "insert into TEST values ('08/20/2016')"
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0181N The string representation of a datetime value is out of range. SQLSTATE=22007

Any ideas?

Regards,
Bruno.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)

On Saturday, August 20, 2016 at 3:35:26 PM UTC-3, Hogan Long wrote:

I don't think this is strange... when you insert you have to use day month year -- always!

When you do a query it uses localization to display the date in a nice way.

What is your question/problem?

On Friday, August 19, 2016 at 5:53:35 PM UTC-4, Bruno Almeida wrote:

Hello friends,

DB2 10.1 LUW

I'm trying to understand this scenario:

$ db2 "create table TEST ( C1 date) "
DB20000I The SQL command completed successfully.

$ db2 "values current date"

1
----------
08/19/2016

1 record(s) selected.

$ db2 "insert into TEST values current date"
DB20000I The SQL command completed successfully.

$ db2 "select * from TEST"

C1
----------
08/19/2016

1 record(s) selected.

Hummm, OK!

Now look ...

$ db2 "insert into TEST values ('08/20/2016')"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned: SQL0181N The string representation of a datetime value is out of range. SQLSTATE=22007

$ db2 "insert into TEST values ('20/08/2016')"
DB20000I The SQL command completed successfully.

$ db2 "select * from TEST"

C1
----------
08/19/2016
08/20/2016

2 record(s) selected.

db2inst1@renata-molinaro:~$ db2 get db cfg | grep -i territory
Database territory = US

Would you help me with this issue?

Regards,
Bruno.

Hi Hogan,

Thanks for your reply.

I have another server installed DB2 9.5 LUW that the date insert format (mm/dd/YYYY) does not return SQL0180N error.

$ db2 connect to DBTEST

Database Connection Information

Database server = DB2/LINUXX8664 9.5.9
SQL authorization ID = DB2INST1
Local database alias = DBTEST

$ db2 "create table TEST ( C1 date) "
DB20000I The SQL command completed successfully.

$ db2 "values current date"

1
----------
08/22/2016

1 record(s) selected.

$ db2 "insert into TEST values current date"
DB20000I The SQL command completed successfully.

$ db2 "select * from TEST"

C1
----------
08/22/2016

1 record(s) selected.

$ db2 "insert into TEST values ('08/20/2016')"
DB20000I The SQL command completed successfully.

$ db2 "insert into TEST values ('20/08/2016')"
DB21034E The command was processed as an SQL statement because it was not a valid Command Line Processor command. During SQL processing it returned: SQL0181N The string representation of a datetime value is out of range. SQLSTATE=22007

$ db2 get db cfg | grep -i territory
Database territory = US

My question is: Why in this case, the format dd/mm/YYYY does not work on CLP?

Regards,
Bruno.

--- SoupGate-Win32 v1.05
* Origin: fsxNet Usenet Gateway (21:1/5)