• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Guru: More Date And Time Conversions Using SQL

    March 26, 2018 Ted Holt

    Since many, if not most, IBM i shops store dates and times in numeric and character fields, it behooves those of us who program those systems to understand all available date- and time-conversion tools. A conversation with a fellow attendee of the recent RPG & DB2 Summit made me realize that I had not written about certain SQL conversion methods.

    IBM i programmers need to convert date, time, and timestamp data from one format to another for at least two reasons. First, we can’t do date and time arithmetic with numeric and character fields. Second, the people whom we serve prefer to read dates and times in formats that they are accustomed to, and we can easily accommodate them.

    Converting To Timestamps

    Let’s consider as an example a table (physical file) of orders. Sales orders, manufacturing orders, assembly orders, whatever. Any type of order that has a due date column (field) will do. For this illustration, we need to consider two columns only — order number and due date. How order number is defined is irrelevant. How due date is defined is all that matters. To begin, consider first the case when due date is defined as an eight-digit packed- or zoned-decimal field.

    OrderNbr DueDate
    1880 20180401
    1881 20180406
    1882 20180403
    1883 20180320
    1884 20180415
    1885 20180507
    1886 20180414
    1887 20180331

    Select all orders that are due within the next 30 days, but do not include past-due orders.

    select * 
      from orders
     where timestamp_format (char(DueDate), 'YYYYMMDD') between
              current date and (current date + 30 days);
    
    OrderNbr DueDate
    1880 20180401
    1881 20180406
    1882 20180403
    1884 20180415
    1886 20180414
    1887 20180331

    The TIMESTAMP_FORMAT function converts character data to a timestamp based on a pattern specified in a format string. A format string value of YYYYMMDD means that the date is stored as a four-digit year, a two-digit month, and a two-digit day. For more information about TIMESTAMP_FORMAT, read Michael Sansoterra’s superb article about DB2 for i 6.1 enhancements.

    Since the due date is numeric, use the CHAR function to convert it to character data.

    Some shops store dates in seven-digit packed-decimal columns.

    OrderNbr DueDate
    1880 1180401
    1881 1180406
    1882 1180403
    1883 1180320
    1884 1180415
    1885 1180507
    1886 1180414
    1887 1180331

    The query is only slightly different.

    select * 
      from orders
    where timestamp_format (char(DueDate+19000000), 'YYYYMMDD') between
             current date and (current date + 30 days);
    

    Adding 19 million to the date converts it to the YYYYMMDD format of the previous example. TIMESTAMP_FORMAT does not have a way to indicate a one-digit century indicator.

    Shops that use eight-byte character dates use an even simpler query, since the date data is already in character format.

    select * 
      from orders
     where timestamp_format (DueDate, 'YYYYMMDD') between
             current date and (current date + 30 days);
    

    Converting Timestamps

    Sometimes we need to convert in the other direction. We need to convert dates, times, or timestamps to other data types. This is also easy to do. TIMESTAMP_FORMAT has a complement: VARCHAR_FORMAT.

    Like TIMESTAMP_FORMAT, VARCHAR_FORMAT accepts two arguments — the data to be converted and a formatting string. See Michael Sansoterra’s article for more information.

    Let’s say that the users are not allowed to adjust due dates themselves, but instead have a way to indicate the orders for which due date needs to be adjusted. The order numbers are stored in a table called ORDER01. A batch process applies the update. Here are three forms of the SQL we need, depending on how due date is defined.

    -- packed- or zoned-decimal, 8 digits
    update orders as ord
       set ord.DueDate = 
             dec(varchar_format (current date + 5 days, 'YYYYMMDD'),8)
     where ord.OrderNbr in (select OrderNbr from order01);
    
    -- packed-decimal, 7 digits
    update orders as ord
       set ord.DueDate = 
       dec(varchar_format (current date + 5 days, 'YYYYMMDD'),8) - 19000000
     where ord.OrderNbr in (select OrderNbr from order01);
    
    -- character 8
    update orders as ord
       set ord.DueDate = varchar_format (current date + 5 days, 'YYYYMMDD')
     where ord.OrderNbr in (select OrderNbr from order01);
    

    All three queries set the due date to five days after the current date.

    These are not the only date-conversion methods. (See my FMTDATE function.) But they are good ones, and I encourage everyone who deals with date and time data to master them.

    RELATED STORIES

    Treasury of new DB2 6.1 (V6R1) Features, Part 5: New Functions and Change Stamp Columns

    IBM Knowledge Center: CHAR

    IBM Knowledge Center: TIMESTAMP_FORMAT

    IBM Knowledge Center: VARCHAR_FORMAT

    Formatting Dates With SQL, Take 3

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags: Tags: 400guru, Four Hundred Guru, Guru, IBM i, SQL

    Sponsored by
    Raz-Lee Security

    Raz-Lee Security is the leader in security and compliance solutions that guard business-critical information on IBM i servers. We are committed to providing the best and most comprehensive solutions for compliance, auditing, and protection from threats and ransomware. We have developed cutting-edge solutions that have revolutionized analysis and fortification of IBM i servers.

    Raz-Lee’s flagship iSecurity suite of products is comprised of solutions that help your company safeguard and monitor valuable information assets against intrusions. Our state-of-the-art products protect your files and databases from both theft and extortion attacks. Our technology provides visibility into how users access data and applications, and uses sophisticated user tracking and classification to detect and block cyberattacks, unauthorized users and malicious insiders.

    With over 35 years of exclusive IBM i security focus, Raz-Lee has achieved outstanding development capabilities and expertise. We work hard to help your company achieve the highest security and regulatory compliance.

    Key Products:

    • AUDIT
    • FIREWALL
    • ANTIVIRUS
    • ANTI-RANSOMWARE
    • MULTI-FACTOR AUTHENTICATION
    • AP-JOURNAL
    • DB-GATE
    • FILESCOPE
    • COMPLIANCE MANAGER
    • FIELD ENCRYPTION

    Learn about iSecurity Products at https://www.razlee.com/isecurity-products/

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    TEMBO Bolsters Database Modernization Kit Insurer Chooses IBM i Database Re-Engineering Over Migration

    2 thoughts on “Guru: More Date And Time Conversions Using SQL”

    • Russ Khoury says:
      March 26, 2018 at 11:01 am

      Hi Ted, thanks for the reminder about TIMESTAMP_FORMAT. For MMDDYY, YYMMDD formats, had to use DIGITS vs CHAR. And 6-digit dates before 2000 don’t convert correctly. All the best.

      Reply
    • GD says:
      February 18, 2021 at 11:57 am

      How would you subtract an 8,0 date from CURRENT DATE ?

      I tried (CURRENT DATE – DATE(TIMESTAMP_FORMAT(CHAR(Date_8_0),’YYYYMMDD’)) )

      And get a nonsense result

      Reply

    Leave a Reply Cancel reply

TFH Volume: 28 Issue: 23

This Issue Sponsored By

  • T.L. Ashford
  • Maxava
  • COMMON
  • Software Concepts
  • Manta Technologies

Table of Contents

  • The Road Ahead For Power Is Paved With Bandwidth
  • Insurer Chooses IBM i Database Re-Engineering Over Migration
  • Guru: More Date And Time Conversions Using SQL
  • TEMBO Bolsters Database Modernization Kit
  • IBM Will Change WebSphere To Work In A Cloudy World

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.