Db/2 – LUW – SQL Error [22007]: “The string representation of a datetime value is out of range”

Background

Playing around with simple queries against our sample Db/2 database.

Received a telling error.

Error

Error Image

SQLError_22007_20180924_1008PM

Error Text

SQL Error [22007]: The string representation of a datetime value is out of range.. SQLCODE=-181, SQLSTATE=22007, DRIVER=4.21.29

Trouble Shooting

Query

Sample Query


SELECT *

from   "Fact"."Sale"  tblFS

WHERE tblFS.InvoiceDateKey between '01-01-2001' AND '12-31-2001'

FETCH FIRST 10 ROWS ONLY

Metadata

Get Database Configuration

There are a couple of choices to get the Database Configuration.

Overview

Inclusive are:

  1. Function
    • SYSPROC.DB_GET_CFG
  2. CLP
    • Get Database Configuration
  3. Query
    • Get System Date

SYSPROC.DB_GET_CFG

Sample Query

SELECT
        DISTINCT

              tbl.NAME

            , VARCHAR(tbl.VALUE, 20)
                AS VALUE

FROM TABLE
        (

            SYSPROC.DB_GET_CFG
            (
                -2
            )

        ) tbl

WHERE
        (

               ( tbl.NAME LIKE '%code%' )
            OR ( tbl.NAME LIKE '%territory%' )
        )   

ORDER BY
          tbl.NAME

Output

SYSPROC.DB_GET_CFG.20180924.1040PM

CLP – Get database Configuration

Syntax

db2 get database configuration

Sample Query

db2 get database configuration | findstr /I "TERRITORY"

Output

getDatabaseConfiguration_20180925_1013AM

Explanation

The problem appears to be that our database territory is set to DEFAU.

Default means DEFAULT.

SQL – Get Date

Sample
SELECT

CURRENT DATE AS "Date ( In DEFAULT FORMAT ) "

FROM SYSIBM.SYSDUMMY1

//

Output

SYSIBM.SYSDUMMY1.CURRENTDATE.20180925.1046AM

Explanation

Our date appears to be ISO, YYYY-MM-DD.

Database Creation Script

Here is our database creation script:


CREATE DATABASE WideWrld
AUTOMATIC STORAGE YES
USING CODESET UTF-8
TERRITORY DEFAULT
COLLATE USING SYSTEM PAGESIZE 4096;

The TERRITORY we offered is DEFAULT.

SUMMARY

BTW, the dates in our original query are 01-01-2001 and 12-31-2001 ( MM-DD-YYYY ); US Dates.

We are good once we revise to ISO ( 2001-01-01 and 2001-12-31).

 

One thought on “Db/2 – LUW – SQL Error [22007]: “The string representation of a datetime value is out of range”

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s