DB2 LUW --- How to change the default date format to 'YYYYMMDD'

Chuan Lu

DB2 LUW --- How to change the default date format to 'YYYYMMDD'

I am a chinese DB2 user, and recently we migrate one sybase application to DB2, and below SQL could execute successful in SYBASE.

create table t_date(c1 date);

 insert into t_date values ('20170909');

It failed in DB2 , and report below errors.

SQL0180N  The syntax of the string representation of a datetime value is
incorrect.  SQLSTATE=22007

How to change the default date format to YYYYMMD, so we needn't to change the application code.

 thanks.

Roy Boxwell

DB2 LUW --- How to change the default date format to 'YYYYMMDD'
(in response to Chuan Lu)
You could use a LOCAL defined DATE FORMAT:

DATE FORMAT field (DATE DECP value)
The DATE DECP value controls the default format that is to be used to represent dates in output.
Acceptable values: ISO, USA, EUR, JIS, LOCAL
Default: ISO
Update: not recommended
dsnhdecp: DATE

The date formats that are associated with the acceptable values for this field are shown in the table below.
Table 37. Date formats
Format name Abbreviation Format Example
International Standards Organization ISO yyyy-mm-dd 2003-12-23
IBM USA standard USA mm/dd/yyyy 12/23/2003
IBM European standard EUR dd.mm.yyyy 23.12.2003
Japanese Industrial Standard Christian Era JIS yyyy-mm-dd 2003-12-23
Locally defined LOCAL your-choice
(by an installation exit routine)


I have *never* tried this of course... and it is then system wide not just one table...

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Bettina Schubert

From: Chuan Lu [mailto:[login to unmask email]
Sent: Wednesday, October 25, 2017 9:03 AM
To: [login to unmask email]
Subject: [DB2-L] - DB2 LUW --- How to change the default date format to 'YYYYMMDD'


I am a chinese DB2 user, and recently we migrate one sybase application to DB2, and below SQL could execute successful in SYBASE.

create table t_date(c1 date);

insert into t_date values ('20170909');

It failed in DB2 , and report below errors.

SQL0180N The syntax of the string representation of a datetime value is
incorrect. SQLSTATE=22007

How to change the default date format to YYYYMMD, so we needn't to change the application code.

thanks.

-----End Original Message-----

Roy Boxwell

DB2 LUW --- How to change the default date format to 'YYYYMMDD'
(in response to Roy Boxwell)
I am an idiot.. I missed the LUW in the subject line... On the little box’s I have no idea – Sorry !

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Bettina Schubert

From: Boxwell, Roy [mailto:[login to unmask email]
Sent: Wednesday, October 25, 2017 10:54 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 LUW --- How to change the default date format to 'YYYYMMDD'

You could use a LOCAL defined DATE FORMAT:

DATE FORMAT field (DATE DECP value)
The DATE DECP value controls the default format that is to be used to represent dates in output.
Acceptable values: ISO, USA, EUR, JIS, LOCAL
Default: ISO
Update: not recommended
dsnhdecp: DATE

The date formats that are associated with the acceptable values for this field are shown in the table below.
Table 37. Date formats
Format name Abbreviation Format Example
International Standards Organization ISO yyyy-mm-dd 2003-12-23
IBM USA standard USA mm/dd/yyyy 12/23/2003
IBM European standard EUR dd.mm.yyyy 23.12.2003
Japanese Industrial Standard Christian Era JIS yyyy-mm-dd 2003-12-23
Locally defined LOCAL your-choice
(by an installation exit routine)


I have *never* tried this of course... and it is then system wide not just one table...

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Bettina Schubert

From: Chuan Lu [mailto:[login to unmask email]
Sent: Wednesday, October 25, 2017 9:03 AM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - DB2 LUW --- How to change the default date format to 'YYYYMMDD'


I am a chinese DB2 user, and recently we migrate one sybase application to DB2, and below SQL could execute successful in SYBASE.

create table t_date(c1 date);

insert into t_date values ('20170909');

It failed in DB2 , and report below errors.

SQL0180N The syntax of the string representation of a datetime value is
incorrect. SQLSTATE=22007

How to change the default date format to YYYYMMD, so we needn't to change the application code.

thanks.

-----End Original Message-----

-----End Original Message-----

Philip Nelson

DB2 LUW --- How to change the default date format to 'YYYYMMDD'
(in response to Roy Boxwell)
This article may help -

https://www.ibm.com/developerworks/data/library/techarticle/0211yip/0211yip3.html

Particularly the section on "Changing the Date Format".

If you specify a particularly territory at database creation time then it
will accept dates in that format (assuming that the format you want is
actually the default date format for that territory). You can't change a
database territory after creation, so if you want to try this you'll need
to create a new database.

If that doesn't work (i.e. you have specified the territory correctly and
the format you want to use isn't a recognized date format in your
territory), then you might be able to do something with INSTEAD OF triggers
on particular tables that need this. Basically this captures the INSERT,
UPDATE or DELETE and performs some other action instead (typically a
modified statement of the same type with extra processing on certain
columns to do the conversions).

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.dbobj.doc/doc/c0023340.html

HTH

Phil

On 25 October 2017 at 10:27, Boxwell, Roy <[login to unmask email]> wrote:

> I am an idiot.. I missed the LUW in the subject line... On the little
> box’s I have no idea – Sorry !
>
>
>
> *Roy Boxwell*
>
> SOFTWARE ENGINEERING GMBH and SEGUS Inc.
> -Product Development-
>
>
> *Heinrichstrasse 83
> https://maps.google.com/?q=Heinrichstrasse+83&entry=gmail&source=g -85
> 40239 Duesseldorf/Germany*
> * Tel. *
>
> *+49 (0)211 96149-675 <+49%20211%2096149675> Fax +49 (0)211 96149-32
> <+49%20211%209614932> Email: **[login to unmask email]* <[login to unmask email]>
> *http://www.seg.de* http://www.seg.de
>
>
>
> * Software Engineering GmbH Amtsgericht Düsseldorf, HRB 37894
> Geschäftsführung: Gerhard Schubert, Bettina Schubert*
>
>
>
> *From:* Boxwell, Roy [mailto:[login to unmask email]
> *Sent:* Wednesday, October 25, 2017 10:54 AM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - RE: DB2 LUW --- How to change the default date
> format to 'YYYYMMDD'
>
>
>
> You could use a *LOCAL* defined DATE FORMAT:
>
>
>
> DATE FORMAT field (DATE DECP value)
>
> The DATE DECP value controls the default format that is to be used to
> represent dates in output.
>
> Acceptable values: ISO, USA, EUR, JIS, LOCAL
>
> Default: ISO
>
> Update: not recommended
>
> dsnhdecp: DATE
>
>
>
> The date formats that are associated with the acceptable values for this
> field are shown in the table below.
>
> Table 37. Date formats
>
> Format name Abbreviation Format Example
>
> International Standards Organization ISO yyyy-mm-dd
> 2003-12-23
>
> IBM USA standard USA mm/dd/yyyy
> 12/23/2003
>
> IBM European standard EUR dd.mm.yyyy
> 23.12.2003
>
> Japanese Industrial Standard Christian Era JIS yyyy-mm-dd
> 2003-12-23
>
> *Locally defined LOCAL your-choice *
>
> *(by an installation exit routine)*
>
>
>
>
>
> I have **never** tried this of course... and it is then system wide not
> just one table...
>
>
>
> *Roy Boxwell*
>
> SOFTWARE ENGINEERING GMBH and SEGUS Inc.
> -Product Development-
>
>
> *Heinrichstrasse 83
> https://maps.google.com/?q=Heinrichstrasse+83&entry=gmail&source=g -85
> 40239 Duesseldorf/Germany*
> * Tel. *
>
> *+49 (0)211 96149-675 <+49%20211%2096149675> Fax +49 (0)211 96149-32
> <+49%20211%209614932> Email: **[login to unmask email]* <[login to unmask email]>
> *http://www.seg.de* http://www.seg.de
>
>
>
> * Software Engineering GmbH Amtsgericht Düsseldorf, HRB 37894
> Geschäftsführung: Gerhard Schubert, Bettina Schubert*
>
>
>
> *From:* Chuan Lu [mailto:[login to unmask email] <[login to unmask email]>]
> *Sent:* Wednesday, October 25, 2017 9:03 AM
> *To:* [login to unmask email]
> *Subject:* [DB2-L] - DB2 LUW --- How to change the default date format to
> 'YYYYMMDD'
>
>
>
> I am a chinese DB2 user, and recently we migrate one sybase application to
> DB2, and below SQL could execute successful in SYBASE.
>
> create table t_date(c1 date);
>
> insert into t_date values ('20170909');
>
> It failed in DB2 , and report below errors.
>
> SQL0180N The syntax of the string representation of a datetime value is
> incorrect. SQLSTATE=22007
>
> How to change the default date format to YYYYMMD, so we needn't to change
> the application code.
>
> thanks.
>
>
>
> -----End Original Message-----
>
>
> -----End Original Message-----
>
> -----End Original Message-----
>

Peter Schwarcz

RE: DB2 LUW --- How to change the default date format to 'YYYYMMDD'
(in response to Philip Nelson)

You might consider using a UDF to check and convert the character string to a date, Here is a sample

CREATE OR REPLACE FUNCTION fn_checkDate (charDate VARCHAR(30) )
RETURNS DATE
SPECIFIC fn_checkDate
LANGUAGE SQL
reads sql data
deterministic
no external action

BEGIN
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE v_charDate VARCHAR(30) ;
DECLARE v_date DATE ;

DECLARE CONTINUE HANDLER FOR SQLSTATE '22007' SET v_date = NULL ;
SET v_charDate = left(charDate,4) || '-' || substr(charDate,5,2) || '-' || right(charDate,2) ;
set v_date = DATE(v_charDate) ;
return v_date ;


END
@

insert into t_date values (fn_checkDate('20170909') )

Regards

Peter Schwarcz

Peter Schwarcz

RE: DB2 LUW --- How to change the default date format to 'YYYYMMDD'
(in response to Peter Schwarcz)

Just following the suggestion by Philip of using an Instead of trigger, you could update the insert statement to use a view name;

create or replace view v_date (c1) as select cast(c1 as varchar(30)) from t_date ;

CREATE OR REPLACE TRIGGER v_date_insert
INSTEAD OF INSERT ON v_date
REFERENCING NEW AS NEW
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
INSERT INTO t_date (c1) VALUES(DATE(left(new.c1,4) || '-' || substr(new.c1,5,2) || '-' || right(new.c1,2))) ;
END

insert into v_date values ('20170909');

Regards
Peter Schwarcz

 

Greg Palgrave

RE: DB2 LUW --- How to change the default date format to 'YYYYMMDD'
(in response to Chuan Lu)

Chuan Lu,

I don't believe LUW allows you to set a default date format to suit your needs.

Philip & Peter have given a couple of suggestions that should work. 

I'm going to suggest that while they may be an interim fix, your best solution is to start using DB2 date formats. If you start to use views or a user-defined function to manage the date format, you may have to change the application code anyway.

If your input data is fixed in the YYYYMMDD format, you can use some of the more recent DB2 built-in functions to easily turn them into valid DB2 dates.

e.g.

VALUES DATE(TO_DATE('20170909','YYYYMMDD'))
1
----------
09-09-2017

(The TO_DATE scalar function is a synonym for the TIMESTAMP_FORMAT scalar function. The DB2 knowledge Center documentation has the full explanation and elements that you can use in the format string.)

DB2 stores all dates internally in the same format, but the display format will depend on your locale settings.

You can input a date in any of the recognised DB2 formats as listed in the KC: https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.sql.ref.doc/doc/r0008474.html

 

Good luck!

Greg

In Reply to Chuan Lu:

I am a chinese DB2 user, and recently we migrate one sybase application to DB2, and below SQL could execute successful in SYBASE.

create table t_date(c1 date);

 insert into t_date values ('20170909');

It failed in DB2 , and report below errors.

SQL0180N  The syntax of the string representation of a datetime value is
incorrect.  SQLSTATE=22007

How to change the default date format to YYYYMMD, so we needn't to change the application code.

 thanks.