Another year is coming to a close and this will be the last entry of 2011 in this blog.

Did you know that DB2 supports the rich cast functions TO_CHAR, TO_DATE, TO_TIMESTAMP and TO_NUMBER?

What makes this functions interesting compared to standard CAST expressions is that they allow you to specify a formatting.

For example if you are in Germany you may want to pretty print dates like this:

VALUES TO_CHAR(CURRENT DATE, 'DD.MM.YYYY');
1
-----------------------
25.12.2011

  1 record(s) selected.

While in the United States you may want to use:

VALUES TO_CHAR(CURRENT DATE, 'MM/DD/YYYY');
1
------------------------
12/25/2011

  1 record(s) selected. 

This is all nice and fine, but what if you want to write SQL that can be shared by sessions connecting from different countries?
In that case you want the formatting to be dependent on a session local session.

When you omit the format these functions derive it from the CURRENT LOCALE LC_TIME register which you can set.
This register is using the rules specified at:CLDR - Unicode Common Locale Data Repository

If you set for example:

SET CURRENT LOCALE LC_TIME = 'CLDR 1.5:de_DE';
VALUES TO_CHAR(CURRENT_DATE);

1
---------------------------------------
25.12.2011 19:52:50

  1 record(s) selected.

That's a good start, but we really only wanted the date portion. Or perhaps we want only a two digit year?

The script below overloads the TO_* functions with a single-parameter version which fills in the format based on a global variable called NLS_DATE_FORMAT and NLS_TIMESTAMP respectively.

SET PATH = ENV, CURRENT PATH; 
SET SCHEMA = ENV;

CREATE OR REPLACE VARIABLE NLS_DATE_FORMAT VARCHAR(40) 
  DEFAULT 'YYYYMMDD HH.MI.SS';

CREATE OR REPLACE VARIABLE NLS_TIMESTAMP_FORMAT VARCHAR(40) 
  DEFAULT 'YYYYMMDD HH.MI.SS';

CREATE OR REPLACE FUNCTION TO_CHAR(stamp TIMESTAMP(12)) 
  RETURNS VARCHAR(100) DETERMINISTIC NO EXTERNAL ACTION CONTAINS SQL 
  RETURN SYSIBM.TO_CHAR(stamp, NLS_DATE_FORMAT);

CREATE OR REPLACE FUNCTION TO_DATE(str VARCHAR(100))
  RETURNS TIMESTAMP(0)
  RETURN SYSIBM.TO_DATE(str, NLS_DATE_FORMAT);

CREATE OR REPLACE FUNCTION TO_DATE(str CHAR(100))
  RETURNS TIMESTAMP(0)
  RETURN SYSIBM.TO_DATE(str, NLS_DATE_FORMAT);

CREATE OR REPLACE FUNCTION TO_TIMESTAMP(str VARCHAR(100))
  RETURNS TIMESTAMP(9)
  RETURN SYSIBM.TO_TIMESTAMP(str, NLS_TIMESTAMP_FORMAT);

CREATE OR REPLACE FUNCTION TO_TIMESTAMP(str CHAR(100))
  RETURNS TIMESTAMP(9)
  RETURN SYSIBM.TO_TIMESTAMP(str, NLS_DATE_FORMAT);

CREATE OR REPLACE FUNCTION ORAENV."||"(arg1 TIMESTAMP(12), arg2 TIMESTAMP(12))
  RETURNS VARCHAR(4000)
  SPECIFIC "||(TSTAMP,TSTAMP)"
  RETURN SYSIBM.TO_CHAR(arg1, NLS_DATE_FORMAT) || SYSIBM.TO_CHAR(arg2, NLS_DATE_FORMAT);

CREATE OR REPLACE FUNCTION ORAENV."||"(arg1 TIMESTAMP(12), arg2 VARCHAR(4000))
  RETURNS VARCHAR(4000)
  SPECIFIC "||(TSTAMP,VARCHAR)"
  RETURN SYSIBM.CONCAT(SYSIBM.TO_CHAR(arg1, NLS_DATE_FORMAT), arg2);

CREATE OR REPLACE FUNCTION ORAENV."||"(arg1 VARCHAR(4000), arg2 TIMESTAMP(12))
  RETURNS VARCHAR(4000)
  SPECIFIC "||(VARCHAR,TSTAMP)"
  RETURN SYSIBM.CONCAT(arg1,SYSIBM.TO_CHAR(arg2, NLS_DATE_FORMAT));

CREATE OR REPLACE FUNCTION ORAENV."||"(TIMESTAMP(12), CHAR())
  RETURNS VARCHAR()
  SPECIFIC "||(TSTAMP,CHAR)"
  SOURCE ORAENV."||"(TIMESTAMP(), VARCHAR());

CREATE OR REPLACE FUNCTION ORAENV."||"(CHAR(), TIMESTAMP(12))
  RETURNS VARCHAR()
  SPECIFIC "||(CHAR,TSTAMP)"
  SOURCE ORAENV."||"(VARCHAR(), TIMESTAMP());

Let's try this out:

SET NLS_DATE_FORMAT = 'DD.MM.YYYY';

VALUES TO_CHAR(CURRENT DATE);
1
-----------------------
25.12.2011

  1 record(s) selected. 

The other way around works as well:

VALUES TO_DATE('25.12.2011');
1
-------------------
2011-12-25-00.00.00
1 record(s) selected.

Note that TO_DATE returns a TIMESTAMP and not, as one may imagine a DATE. If you want true dates, you can overload TO_DATE to do just that as well.
Also note that I have above also provided functions that overload the string concatenation.

 

Happy Holidays! My new years resolution shall be to find more interesting SQL Tips for DB2 next year.