Wednesday, March 11, 2015

Change date from one format to another using SQL

converting date format 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:

 

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

11 comments:

  1. still combination of BIFs in one statement of free rpg/ile rpg looks better ...

    ReplyDelete
  2. Try a date format of 'MMDDRR'. This uses the current year to determine the date year. So your 2095 would instead be 1995.
    http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.5.0/com.ibm.db2.luw.sql.ref.doc/doc/r0007107.html

    Chris Ringer

    ReplyDelete
  3. This is a very good article. Also the varchar_format is great for turning character strings into timestamps and does not require the concatenation shown in the article.

    Another technique I like to use SQL for is to set date values using the SQL set command and also to use the file sysibm.sysdummy1 to retrieve static values.

    ReplyDelete
  4. As an asside, if you need the date 22195 converted into 1995-01-22, you need the format 'MMDDRR' instead of 'MMDDYY'.

    Timestamp format already converts a date into a timestamp, so neither surrounding it with the scalar function date nor with the scalar function char is necessary.

    Birgitta

    ReplyDelete
  5. Likely the "95" date was from 1995, so the MMDDRR, which was new to me, would be best. Nice article and comments.

    ReplyDelete
  6. What about converting 8-digit numeric date; would it work the same way too? I hate using substring function after converting it to Char format and inserting '/' to format the date in appropriate format. Thanks.

    ReplyDelete
    Replies
    1. 8 long is not problem you just change the code to:
      select char(date(timestamp_format(digits(number8),'MMDDYYYY')),iso)

      Delete
  7. Is there any way to select valid dates only using sql? (Without creating a SQL function?)

    ReplyDelete
  8. CYYMMDD to date
    date(timestamp_format(substr(CYYMMDD,2,6),'RRMMDD'))
    assuming dates in current century.

    ReplyDelete
  9. can we have output without separates ?

    ReplyDelete
    Replies
    1. The output variable is a date column, and all date variables have to have date separators.
      This is not a number or character column.

      Delete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.