Wednesday, June 23, 2021

Providing the same date in different formats using SQL

using sql to_char and varchar_format to format date and timestamp into different date formats

I have been working with a group of Windows programmers to interface data from an IBM i partition to a Microsoft SQL database. The biggest issue I had was the formats of the dates. These Windows programmers insisted I pass all the dates as character format including the slash character ( / ) as the separator, which is no big deal. What had us going around in circles was that the same date would need to be provided in different date formats depending on the part of the interface. As I am in the USA most of the time they wanted me to pass the date in MDY format (MM/DD/YY), sometimes in USA format (MM/DD/YYYY), and for a couple of dates I need to pass them in the European DMY format (DD/MM/YY).

The date was coming from a DDS physical file. To illustrate how I could simply provide the date in the formats they desired I have created a file, called TESTFILE:

01  A          R TESTFILER
02  A            TEST_DATE       L
03  A            TEST_STAMP      Z

Line 2: TEST_DATE is a true date field (not a number pretending to be a date).

Line 3: TEST_STAMP is a timestamp field.

I can insert my single record of test data with the following SQL statement:

INSERT INTO TESTFILE 
  VALUES(CURRENT_DATE,CURRENT_TIMESTAMP) ;

The SQL special values give away their functions. CURRENT_DATE is today's date, and CURRENT_TIMESTAMP is the today's date and the current time.

I can view this inserted record using the following SQL statement:

SELECT * FROM TESTFILE ;

The results show the record's contents:

TEST_DATE    TEST_STAMP
----------   --------------------------
2021-06-11   2021-06-11 14:31:05.255945

I have written about the TO_CHAR and VARCHAR_FORMAT SQL functions previously. They do the same thing, convert numbers, dates, and timestamps to character format, and I can format them with separator characters. Alas, I cannot format time values.

In my first example I am going to take the date field from the file, TEST_DATE, and format it into different format:

01  SELECT TEST_DATE,
02         TO_CHAR(TEST_DATE, 'MMDDYY') AS "MDY no /",
03         VARCHAR_FORMAT(TEST_DATE, 'MM/DD/YY') AS "MDY",
04         TO_CHAR(TEST_DATE, 'MM/DD/YYYY') AS "USA",
05         VARCHAR_FORMAT(TEST_DATE, 'DD/MM/YY') AS "DMY"
06  FROM TESTFILE ;

Line 1: As a reminder I will show the unformatted date in the first column of the results.

Line 2: When I use the TO_CHAR with a date I have to pass two parameters:

  1. Name of the date field. In this line it is TEST_DATE
  2. Format I want the date returned in. In this example I want the date returned as MDY with no separators

Line 3: I am using the VARCHAR_FORMAT here just to show it is interchangeable with the TO_CHAR. For this column I want the date in MDY with the slash as the separator.

Line 4: Back to using the TO_CHAR to return the date in USA format.

Line 5: Using the VARCHAR_FORMAT to return the date in DMY format.

When I do this in my way at work I just use the TO_CHAR as it is less characters to type.

Next up is the timestamp field, TEST_STAMP:

01  SELECT TEST_STAMP,
02         TO_CHAR(DATE(TEST_STAMP), 'MM/DD/YY') AS "Date"
03  FROM TESTFILE ;

The results show that the second column has a formatted date the way I desired:

TEST_STAMP                   Date
--------------------------   --------
2021-06-11 14:31:05.255945   06/11/21

When I was providing this data to the interface I built a SQL View for two reasons:

  1. I can format the View's results any way I want, like providing the date in multiple formats, without having to change the source of the data
  2. The data cannot be "accidentally" changed by the application using it

If I was to build a view over TESTFILE, like I did with the interface file, it would look like:

01  CREATE OR REPLACE VIEW MYLIB.TESTFILE_VIEW
02    FOR SYSTEM NAME "TESTFILEV"
03   (TEST_DATE,TEST_DATE_MDY,TEST_DATE_USA,TEST_DATE_DMY,
04    TEST_STAMP,TEST_STAMP_DATE)
05  AS
06  (SELECT TEST_DATE,
07          TO_CHAR(TEST_DATE, 'MM/DD/YY'),
08          TO_CHAR(TEST_DATE, 'MM/DD/YYYY'),
09          TO_CHAR(TEST_DATE, 'DD/MM/YY'),
10          TEST_STAMP,
11          TO_CHAR(DATE(TEST_STAMP), 'MM/DD/YY')
12   FROM TESTFILE) ;

Line 1: I am giving my View a long name (greater than ten characters).

Line 2: If I use a long name I always like to give my View a short system name.

Lines 3 and 4: As I am creating new columns in the View, therefore, I need to give a column list so that the new columns will have a name.

Lines 6 – 12: I have combined the two previous SQL statements to give one to give all the dates.

The results look like:

            TEST_     TEST_       TEST_
TEST_DATE   DATE_MDY  DATE_USA    DATE_DMY
----------  --------  ----------  --------
2021-06-11  06/11/21  06/11/2021  11/06/21


                            TEST_
TEST_STAMP                  STAMP_DATE
--------------------------  ----------
2021-06-11 14:31:05.25594   06/11/21

Fortunately this was a simple for a solution for a "problem" I would not have thought would have been an issue.

 

This article was written for IBM i 7.4, and should work for some earlier releases too.

3 comments:

  1. thanks to the tips... in any case, I think that any external serialization form and representation using strings, with the task of data exchange between system, should use ISO date format, IMHO...

    ReplyDelete
    Replies
    1. I agree with you. *ISO is the best format for interfaces.

      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.