Date format weird behavior

[Bruno Almeida]

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.

[Hogan Long]

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?

[CRPence]

On 19-Aug-2016 14:53 -0700, Bruno Almeida wrote:
>
> DB2 10.1 LUW
>
> I'm trying to understand this scenario:
>

> <<SNIP>>

>
> $ 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?
>

If all that is required is to have a circumvention, then I suspect
that one or both of the following should assist to effect the insert of
a date literal representing 20-Aug-2016, irrespective the /territory/ or
other date-formatting attributes established for the DB2 LUW Command
Line Processor (CLP):

db2 "insert into TEST values ( date'2016-08-20' )"

db2 "insert into TEST values ( '2016-08-20' )"

For reference, the following document suggests a revision to the "CLP
packages" binding established for date-formatting; perhaps of possible
assistance in the scenario of the OP, despite the quite specific nature
of the issue being quite different:

[http://www.ibm.com/support/docview.wss?uid=swg21437999]
SQL0180N error from the DB2 Command Line Processor (CLP) when date
with default format concatenated with timestamp …
Reference #: 1437999
"…
What are the solutions available to change from one format to another?

Users can follow the below methods to resolve the problem:

• Bind the CLP packages using the ISO format

db2 bind @db2ubind.lst DATETIME ISO SQLERROR CONTINUE

…"

FWiW:

Perhaps not very helpful to offer, but the IBM DB2 for i would have
accepted the value for the first of the INSERT requests quoted above
[i.e. the failing INSERT], and would have rejected the second of the
INSERT requests quoted above [i.e. the successful INSERT] with the same
sqlcode\sqlstate as the above-quoted failing INSERT; i.e. the opposite
effect would have been exhibited using a DB2 for i SQL session, and that
remains true, irrespective the date-formatting attributes established:

set current schema qtemp
-- SET CURRENT SCHEMA statement complete.
create table test ( c1 date )
-- Table TEST created in QTEMP.

insert into TEST values current date

-- 1 rows inserted in TEST in QTEMP.
select * from TEST
-- SELECT statement run complete.
-- the report was two row values: 08/20/2016

insert into TEST values ('08/20/2016')

-- 1 rows inserted in TEST in QTEMP.

insert into TEST values ('20/08/2016')

-- SQL0180 "Value in date, time, or timestamp string not valid."

The reason for that opposite effect, is that with the DB2 for i, the
string representation for a date, the one that was shown being used,
matches to the "IBM® USA standard" format of "mm/dd/yyyy"; i.e. the
##/##/####, where # represents a digit, and per the slashes in those
positions, implies that the *USA* standard format defines the
date-string-value. That USA _standard_ format is consistently
documented in both of the DB2 for i and the DB2 LUW. Thus I would
expect the sqlcode -181 for the string '20/08/2016', but expect no error
to be issued for the string '08/20/2016'.

However I agree with the comment made by Hogan in another followup
reply, about the nature of the issue, being essentially, that there is
an expectation of a possible difference between the displayed-formatting
of date values and the input-formatting for date values. I would be
frustrated nevertheless, by the effect seen; similarly to what I infer
the OP alludes. That is because I am quite used to [and even reliant
upon] the DB2 for i SQL /understanding/ all of the "standard" formats,
irrespective the /local/ formatting I have defined for input of
non-standard date-values.

I suspect that despite the Territory=US being in effect for the
formatting of *displayed* date values, there is quite likely to be
_another setting_ for the formatting required for the *input* of date
values; in the scenario of the OP, for which the expectation has been
established with the DB2 LUW CLP, that a date string must be formatted
as 'dd/mm/yyyy'. Essentially, I wonder if, there might be an override
of the /standard/ USA format that has the DB2 LUW CLP insisting that the
string-value-as-date must be formatted as 'dd/mm/yyyy'; that
essentially, per that insistence, the ability of the CLP to accept the
standard 'mm/dd/yyyy' date-string values is _necessarily nullified_ due
to the conspicuous conundrum of accepting both formats. FWiW, the DB2
for i could not exhibit that same behavior, simply because, there is _no
ability_ to override\insist that the DB2 for i must accept
input-date-strings with the format 'dd/mm/yyyy'; the non-standard [i.e.
the "LOCAL" or "LOC"; see the CHAR scalar function] string
representations of dates are limited to formats with two-digit years.

For reference, an older doc link for the sqlstate=22007:
[https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.7.0/com.ibm.db2.luw.messages.sql.doc/doc/msql00181n.html]

For reference, the following docs also from DB2 LUW 9.7 is [best I
can recall] consistent with all releases of DB2 for i:

[https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.7.0/com.ibm.db2.luw.sql.ref.doc/doc/r0008474.html]
"…
_Date strings_

A string representation of a date is a string that starts with a digit
and has a length of at least 8 characters. Trailing blanks may be
included; leading zeros may be omitted from the month and day portions.

Valid string formats for dates are listed in the following table. Each
format is identified by name and associated abbreviation.

Table 1. Formats for String Representations of Dates
Format Name Abbrev Date Format Example
International Standards Organization ISO yyyy-mm-dd 1991-10-27
IBM® USA standard USA USA mm/dd/yyyy 10/27/1991
IBM European standard EUR dd.mm.yyyy 27.10.1991
Japanese Industrial Std Christian Era JIS yyyy-mm-dd 1991-10-27
Site-defined LOC Depends on
territory code -
of application
…"

--
Regards, Chuck

[CRPence]

On 20-Aug-2016 14:14 -0700, CRPence wrote:
> <<SNIP>>

> For reference, the following document suggests a revision to the
> "CLP packages" binding established for date-formatting; perhaps of
> possible assistance in the scenario of the OP, despite the quite
> specific nature of the issue being quite different:
>
> [http://www.ibm.com/support/docview.wss?uid=swg21437999]
> SQL0180N error from the DB2 Command Line Processor (CLP) when date
> with default format concatenated with timestamp …
> Reference #: 1437999
> "…
> What are the solutions available to change from one format to
> another?
>
> Users can follow the below methods to resolve the problem:
>
> • Bind the CLP packages using the ISO format
>
> db2 bind @db2ubind.lst DATETIME ISO SQLERROR CONTINUE
>
> …"
>

> <<SNIP>>

>
> I suspect that despite the Territory=US being in effect for the
> formatting of *displayed* date values, there is quite likely to be
> _another setting_ for the formatting required for the *input* of
> date values; in the scenario of the OP, for which the expectation has
> been established with the DB2 LUW CLP, that a date string must be
> formatted as 'dd/mm/yyyy'. Essentially, I wonder if, there might be
> an override of the /standard/ USA format that has the DB2 LUW CLP
> insisting that the string-value-as-date must be formatted as
> 'dd/mm/yyyy'; that essentially, per that insistence, the ability of
> the CLP to accept the standard 'mm/dd/yyyy' date-string values is
> _necessarily nullified_ due to the conspicuous conundrum of accepting
> both formats. FWiW, the DB2 for i could not exhibit that same
> behavior, simply because, there is _no ability_ to override\insist
> that the DB2 for i must accept input-date-strings with the format
> 'dd/mm/yyyy'; the non-standard [i.e. the "LOCAL" or "LOC"; see the
> CHAR scalar function] string representations of dates are limited to
> formats with two-digit years.

Whilst looking for docs on something similar, I found a page of
documentation that I missed in research for my prior reply, but is
related to both of the above comments; NB, what is *recognized* as valid
when the "local format for date *conflicts* with an ISO, JIS, EUR, or
USA date format":

DB2 for Linux UNIX and Windows 9.7.0->Database application
development->Multicultural support->Application development
considerations->Date and time formats by territory code
[https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.7.0/com.ibm.db2.luw.admin.nls.doc/doc/r0004572.html]
"…
Following is a description of the input and output formats for date and
time:

• …
• Input Date Format
• There is no default input date format
• Where the local format for date conflicts with an ISO, JIS, EUR,
or USA date format, the local format is recognized for date input.
• …
…

You can use the command line to change the default date format to ISO
(YYYY-MM-DD) 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 ISO blocking all grant public

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

--
Regards, Chuck

[Bruno Almeida]

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.

[CRPence]

On 22-Aug-2016 05:47 -0700, Bruno Almeida wrote:
> <<SNIP>>

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

AIUI the /binding/ established for the default date-format used for
date-value-input differs for the two connections; for the database from
the OP, the local format apparently has been established as dd/mm/yyyy,
whereas the local format for database referenced in the above-quoted
message apparently has been established as mm/dd/yyyy [which matches the
USA /standard/ format, though not the USA /territory/ format that is
apparently mm-dd-yyyy]. The established DATETIME need not match the
established "territory code" of the database:

DB2 for Linux UNIX and Windows 9.7.0->Database

administration->Interfaces (Tools, Commands, APIs)->Commands->CLP
commands->BIND
[https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.7.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0001935.html]
"_BIND command_

Invokes the bind utility, which prepares SQL statements stored in the
bind file generated by the precompiler, and creates a package that is
stored in the database.

_Scope_

This command can be issued from any database partition in db2nodes.cfg.
It updates the database catalogs on the catalog database partition. Its
effects are visible to all database partitions.

Notes:
1. If the server does not support the DATETIME DEF option, it is
mapped to DATETIME ISO.
…

_DATETIME_
Specifies the date and time format to be used.

DEF
Use a date and time format associated with the territory code
of the database.

EUR
Use the IBM® standard for Europe date and time format.

ISO
Use the date and time format of the International Standards
Organization.

JIS
Use the date and time format of the Japanese Industrial Standard.

LOC
Use the date and time format in local form associated with the
territory code of the database.

USA
Use the IBM standard for U.S. date and time format.

…"

For reference, for the DEF as "Default territory code":
[https://www.ibm.com/support/knowledgecenter/en/SSEPGG_9.7.0/com.ibm.db2.luw.admin.nls.doc/doc/r0004572.html]
_Date and time formats by territory code_

--
Regards, Chuck

[Bruno Almeida]

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.

[CRPence]

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