Providing the same date in different formats using SQL

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.