Guru: Date Format Confusion

May 10, 2021 Ted Holt

Hey, Ted:

I use the Copy to Import File (CPYTOIMPF) command to build a CSV file to be sent to a MySQL system. My colleagues are unable to import the file because of the format of a timestamp field. CPYTOIMPF puts a hyphen between the date and time (e.g. 2021-05-10-04.36.24.849555), but the other system wants a space between them. Other than writing a custom program do you know of another way to change the format of the timestamp output?

— Richard

You won’t need a custom program, Richard. There’s another way to handle this requirement.

IBM has already very thoughtfully provided us with some common date, time, and timestamp formats, but it would be impossible for IBM to foresee every format we might need. For those times, we have other tools.

In your case, I suggest you create an SQL view that uses the TO_CHAR function to format the timestamp the way you need it.

create view SomeView as
( select this, that, the_other,
         to_char(TStamp, 'yyyy-mm-dd hh.mi.ss') as TStamp
  from SomeTable);

The second parameter of the TO_CHAR function lets you specify how you want the date/time value formatted. The result is a character field that will go into your stream file quite nicely.

Now, reference the view in the CPYTOIMPF command.

CPYTOIMPF FROMFILE(SOMEVIEW)
          TOSTMF('/somedir/somefile.csv')
          STMFCODPAG(*PCASCII)      
          RCDDLM(*CRLF)   

Voilà! That’s all there is to it.

There is a lot of confusion about dates, times, and timestamps. Another reader, whom I’ll call Bob, contacted me about the same time Richard did with a question about date formats. Bob said that his people wanted a date stored in a physical file in MM-DD-YYYY format (e.g., 05-10-2021). He was trying to find a DATFMT value to put in either the DDS of the physical file or an RPG definition (“D”) spec that would give him that format. Of course, he did not find one.

I suspect the confusion about date/time formats is due to the fact that for many years midrange programmers had to store dates and times in numeric and alphanumeric (character) fields. (I have never worked on a system where date and time were stored together in one numeric or alpha field, but I suppose it’s been done.) In those days, we had to know how a date or time was stored in order to use it properly.

Times have changed. Now we have the date/time data types. I pointed out to Bob that a date is a date is a date, regardless of the format specified on a DDS spec or an RPG D spec. When you define a column (field) to be of date, time, or timestamp data type, the system stores the value in an internal format, and the good news is that we don’t have to know what that internal format looks like.

When someone says he wants a date stored in a certain format, what he likely means is that he wants to see the date in that format, and that, my friend, is more good news. If I prefer to view a certain date column in month-day-year format and you prefer to view the same date in day-month-year format, we both win!

Bob’s problem is solved as Richard’s was.

select to_char(SomeDate, 'mm-dd-yyyy') from SomeTable;

In my opinion, literal thinking is one of the reasons many IBM i shops are stuck in the 1990s.

RELATED STORY

Guru: Formatting Numbers and Dates/Times/Timestamps in SQL