• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • 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

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags: Tags: 400guru, CPYTOIMPF, CSV, FHG, Four Hundred Guru, IBM i, MySQL, SQL

    Sponsored by
    DRV Technologies, Inc.

    Get More from Your IBM i

    Many users today struggle to get at the data they need on the IBM i. When users get reports, they look like they were formatted some time last century.

    Some organizations are still printing pre-printed forms and checks on impact printers.

    How often do operators log on to their system to look for messages they hope they don’t find?

    All of these scenarios can affect users’ perception of the IBM platform negatively, but there are simple solutions.

    DRV Technologies Inc. develops innovative solutions that help customers get more from their IBM i systems.

    Solutions include:

    • SpoolFlex spool conversion & distribution
    • FormFlex electronic forms
    • SecureChex MICR laser check printing
    • MessageFlex system monitoring

    FlexTools streamline resources, improve efficiency and enable pro-active system management.

    Better software, better service, DRV Tech.

    Learn how you can get more from your IBM i at www.drvtech.com

    Call 866 378-3366 for a Free Demonstration

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Precisely To Bolster Data Governance With Infogix Buy Thoroughly Modern: IBM i Web Development Trends To Watch In the Second Half

    2 thoughts on “Guru: Date Format Confusion”

    • Ken Harding says:
      May 10, 2021 at 8:38 am

      Ted, nice work. Thanks for sharing. I always enjoy and learn something from your articles.

      Reply
    • Leslie Turner says:
      May 11, 2021 at 6:44 am

      Thanks Ted! How long have we had the TO_CHAR function?

      Reply

    Leave a Reply Cancel reply

TFH Volume: 31 Issue: 35

This Issue Sponsored By

  • UCG Technologies
  • Fresche Solutions
  • IBM
  • Computer Keyes
  • Raz-Lee Security

Table of Contents

  • Talking Shop With Power Systems Chief Stephen Leonard
  • Thoroughly Modern: IBM i Web Development Trends To Watch In the Second Half
  • Guru: Date Format Confusion
  • Precisely To Bolster Data Governance With Infogix Buy
  • IBM i PTF Guide, Volume 23, Number 19

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • N2i Gains Traction Among IBM i Newbies
  • Realizing The Promise Of Cross Platform Development With VS Code
  • 2023 IBM i Predictions, Part 3
  • Four Hundred Monitor, January 25
  • Join The 2023 IBM i Marketplace Survey Webinar Tomorrow
  • It Is Time To Have A Group Chat About AI
  • 2023 IBM i Predictions, Part 2
  • Multiple Vulnerabilities Pop Up In Navigator For i
  • Participate In The 2023 IBM i Marketplace Survey Discussion
  • IBM i PTF Guide, Volume 25, Number 4

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2022 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.