Change date from one format to another using SQL

This post is inspired by a question I was asked by a colleague. He wanted to create a SQL statement that would convert a MDY "date", in a six alphanumeric field in a file, to a ISO "date", in a ten alphanumeric variable.

In RPG I could simply do the following:

test(de) *mdy0 InField ; if (%error) ; OutField = ' ' ; else ; OutField = %char(%date(InField:*mdy0):*iso) ; endif ;

Before I start showing examples of SQL let me introduce the file I will be using, TESTFILE, which looks like:

A R TESTFILER A RECORD 1 A ALPHA 6 A NUMBER 6 0 A K RECORD

I added the following values into the file. Some are valid dates, others are not:

RECORD	ALPHA	NUMBER
1	122514	122,514
2	022195	22,195
3		0
4	010201	10,201
5	333333	333,333
6	300310	300,310

I am going to use two SQL functions in all of these examples. The first DATE will convert my alphanumeric field to a date. The second, CHAR will convert the date to a character/alphanumeric value, in the format I give as the second parameter to the CHAR function.

Below is a simple example where I take a date, January 28 2015, convert it to a date using the DATE function, and then back to a character value using the CHAR in ISO format:

This is best shown in the simple example below:

select char(date('01/28/15'), iso) as converted_date from testfile

The output looks like:

CONVERTED_DATE 2014-12-25

My first thought was to take the "date" in the field ALPHA, insert slashes ( / ) into it and then do what I have shown above:

select char(date(substring(alpha,1,2) || '/' || substr(alpha,3,2) || '/' || substr(alpha,5,2)), iso) as converted_date from testfile

I deliberately used both SUBSTRING and SUBSTR top show that either, or both, could be used.

The output is:

RECORD	ALPHA	CONVERTED_DATE
1	122514	2014-12-25
2	022195	1995-02-21
3		++++++++++
4	010201	2001-01-02
5	555555	++++++++++
6	300310	++++++++++

"++++++++++" is how null is displayed as the value in ALPHA could not be converted from MDY to a valid date.

The SQL statement looks messy. I soon found a cleaner alternative without having to insert slashes into ALPHA, by using the TIMESTAMP_FORMAT function followed by the date format, see below:

select char(date(timestamp_format(alpha,'MMDDYY')),iso) as converted_date from testfile

I was surprised to find that the output was different. If you look at number 2 you will see that the converted output is 2095-02-21, not 1995-02-21 as in the previous example.

RECORD	ALPHA	CONVERTED_DATE
1	122514	2014-12-25
2	022195	2095-02-21
3		++++++++++
4	010201	2001-01-02
5	555555	++++++++++
6	300310	++++++++++

I can easily change the output format by just changing the second parameter in the CHAR. For example, I am going to change the output to USA:

select char(date(timestamp_format(alpha,'MMDDYY')),usa) as converted_date from testfile

RECORD	ALPHA	CONVERTED_DATE
1	122514	12/25/2014
2	022195	02/21/2095
3		++++++++++
4	010201	02/01/2001
5	555555	++++++++++
6	300310	++++++++++

As most of the rest of world does their dates in DMY format I can take ALPHA and convert it to DMY by changing the format in the TIMESTAMP_FOPRMAT.

select char(date(timestamp_format(alpha,'DDMMYYY')),iso) as converted_date from testfile

A number of the "dates" in ALPHA are not valid as dates in DMY format, so they are output as null. The fourth date changes from February 1 2010 to January 2 2010.

RECORD	ALPHA	CONVERTED_DATE
1	122514	++++++++++
2	022195	++++++++++
3		++++++++++
4	010201	2001-01-02
5	555555	++++++++++
6	300310	2010-03-30

If I try and the same statement for the numeric field NUMBER I get more invalid dates.

select char(date(timestamp_format(char(number),'MMDDYY')),iso) as converted_date from testfile

The CHAR function, like RPG's %CHAR<, drops any leading zeroes, therefore 01234 becomes 1,23. This meant that any "date" with a leading zero became a 5 long value, which on the most part could not be converted to a date. The exception was number four that was mapped to October 20 2001, rather than January 2 2001.

RECORD	NUMBER	CONVERTED_DATE
1	122,514	2014-12-25
2	22,195	++++++++++
3		++++++++++
4	10,201	2001-10-20
5	555,555	++++++++++
6	300,310	++++++++++

The way to overcome this is to use the DIGITS function rather than the first CHAR:

select char(date(timestamp_format(digits(number),'MMDDYY')),iso) as converted_date from testfile

RECORD	NUMBER	CONVERTED_DATE
1	122,514	2014-12-25
2	22,195	2095-02-21
3		++++++++++
4	10,201	2001-01-02
5	555,555	++++++++++
6	300,310	++++++++++

There are other date formats you can use. By using the logic I have given above you should be able to use them.

 

March 16: update for the '2095' finding here.

 

You can learn more about these on the IBM website:

• SQL DATE
• SQL CHAR
• SQL TIMESTAMP_FORMAT
• SQL DIGITS

 

This article was written for IBM i 7.2, and it should work with earlier releases too.