• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • Guru: Formatting Numbers and Dates/Times/Timestamps in SQL

    January 13, 2020 Paul Tuohy

    In this article, I want to share with you an SQL scalar function that I happen to have been using quite a bit recently. At times, when using an SQL select statement, you may want to format a number or date. Something along the same lines as using the %EDITC or %EDITW built in functions in RPG or the EDTCDE or EDTWRD keywords in DDS. In SQL we can use the VARCHAR_FORMAT or TO_CHAR (they are synonyms for each other – both work exactly the same way) scalar function to provide similar functionality.

    Since they are synonyms for each other, I am going to use TO_CHAR in the rest of the article, but feel free to substitute VARCHAR_FORMAT in its place.

    To demonstrate the use of TO_CHAR I created four global variables and populated them as follows:

    create variable stamp timestamp;
    create variable isdate date;
    create variable amount decimal (7, 2);
    create variable negative_amount decimal (7, 2);
    
    set stamp = current timestamp;  -- 2019-12-29 16:32:40.889085
    set isdate = current date;      -- 2019-12-29
    set amount = 1234.56;           -- 1234.56
    set negative_amount = -1234.56; -- -1234.56
    

    The format of TO_CHAR is:

    TO_CHAR(numeric expression or timestamp expression, format string)
    

    The first parameter is the numeric or date/time/timestamp expression to be evaluated and the second parameter is the required format. The format is a template as to how the value is to be formatted

    The formatting rules are, as one would expect, different for a number as opposed to a date/time/timestamp. Let’s start with numbers.

    The template represents each significant digit using a 0 or a 9. With 0, leading zeros are shown as zero. With 9, leading zeros are replaced with blanks.

    values 
     to_char(amount, '99999.99') 
    ,to_char(negative_amount, '99999.99')
    ,to_char(amount, '00000.00') 
    ,to_char(negative_amount, ‘00000.00');
    

     

    The template should match the definition of the variable and the placement of the decimal separator should match the definition of the decimal. If there are too few decimal places, the number is rounded. If there are too many decimal places, zeros are added.

    values 
     to_char(amount, '99999.9') 
    ,to_char(amount, '99999.999')
    ,to_char(amount, '9999.99')
    ,to_char(amount, '999.99');
    

    A dollar sign can be added to the start of the string and a comma may be specified as a group separator. If dollar for currency, comma for group separator, and period for decimal separator do not fit your needs, you can use L for currency symbol character, G for group separator character, and D for decimal point character. The three values are based on information retrieved from message CPX8416 in message file QCPFMSG in the library list. Note that the characters must be in uppercase.

    values 
     to_char(amount, '$09,999.99PR')
    ,to_char(negative_amount, '$09,999.99') 
    ,to_char(amount, 'L09G999D99')
    ,to_char(negative_amount, 'L09G999D99');
    

    The minus sign can be placed at the end of the number using MI or negative number can be placed between angle brackets using PR.

    values 
     to_char(amount, 'L09G999D99MI')
    ,to_char(negative_amount, 'L09G999D99MI')
    ,to_char(amount, 'L09G999D99PR')
    ,to_char(negative_amount, 'L9999D99PR');
    

    When formatting a date, time, or timestamp, the variable you specify is cast as a timestamp. The format template can use a minus sign, period, slash, comma, apostrophe, semicolon, colon, or blank as a separator. The casing of the special formatting characters is relevant for names but irrelevant for numbers.

    Days may be identified using DD for the day of the month, DAY (Day or day) for the day name, and DY (Dy or dy) for the abbreviated day name.

    Months may be identified using MM for the month of the year, MONTH (Month or month) for the month name, and MON (Mon or mon) for the abbreviated month name.

    A four, three, two or one digit year may be identified using YYYY, YYY, YY and Y.

    values 
     to_char(isdate, 'yyyy-mm-dd')  -- *ISO
    ,to_char(isdate, 'mm-dd-yyyy')  -- *USA
    ,to_char(isdate, 'Day, dd Month yyyy')
    ,to_char(isdate, 'Dy, dd Mon yyyy')
    ,to_char(isdate, 'DY, dd MON yyyy');
    

    Hours may be identified using HH (or HH12) for a 12-hour format or HH24 for a twenty-four hour format.

    Minutes may be identified using MI.

    AM, A.M., PM, or P.M. may be used as a meridian indicator.

    values 
     to_char(stamp, 'hh24:miAM Day, dd Month yyyy')
    ,to_char(isdate, 'hh24:mi Day, dd Month yyyy');
    

     

    Other date format elements that you may find useful are J for the Julian date, D for day of week, ID for the ISO day of the week, DDD for day of year, ID for the ISO day of the week, W for week of month, WW for week of the year, IW for the ISO week of the year, Q for quarter, IYYYY (IYYY, IYY, IY) for the ISO year, CC for the century.

    When dealing with timestamps you can also use SS for seconds, SSSSS for seconds since previous midnight, MS for Milliseconds, US or NNNNNN for Microseconds.

    I hope you find VARCHAR_FORMAT/TOCHAR as useful as I do.

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

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

    Sponsored by
    WorksRight Software

    Do you need area code information?
    Do you need ZIP Code information?
    Do you need ZIP+4 information?
    Do you need city name information?
    Do you need county information?
    Do you need a nearest dealer locator system?

    We can HELP! We have affordable AS/400 software and data to do all of the above. Whether you need a simple city name retrieval system or a sophisticated CASS postal coding system, we have it for you!

    The ZIP/CITY system is based on 5-digit ZIP Codes. You can retrieve city names, state names, county names, area codes, time zones, latitude, longitude, and more just by knowing the ZIP Code. We supply information on all the latest area code changes. A nearest dealer locator function is also included. ZIP/CITY includes software, data, monthly updates, and unlimited support. The cost is $495 per year.

    PER/ZIP4 is a sophisticated CASS certified postal coding system for assigning ZIP Codes, ZIP+4, carrier route, and delivery point codes. PER/ZIP4 also provides county names and FIPS codes. PER/ZIP4 can be used interactively, in batch, and with callable programs. PER/ZIP4 includes software, data, monthly updates, and unlimited support. The cost is $3,900 for the first year, and $1,950 for renewal.

    Just call us and we’ll arrange for 30 days FREE use of either ZIP/CITY or PER/ZIP4.

    WorksRight Software, Inc.
    Phone: 601-856-8337
    Fax: 601-856-9432
    Email: software@worksright.com
    Website: www.worksright.com

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    As I See It: Digital Trafficking SAP Sending Mixed Messages On ERP Platform Support

    Leave a Reply Cancel reply

TFH Volume: 30 Issue: 3

This Issue Sponsored By

  • TL Ashford
  • ProData Computer Services
  • RPG & DB2 Summit
  • Computer Keyes
  • TL Ashford

Table of Contents

  • There’s Always A New Last Laugh With Legacy
  • SAP Sending Mixed Messages On ERP Platform Support
  • Guru: Formatting Numbers and Dates/Times/Timestamps in SQL
  • As I See It: Digital Trafficking
  • IBM i PTF Guide, Volume 22, Number 1
  • Participate In The 2020 IBM i Marketplace Survey Webcast

Content archive

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

Recent Posts

  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25
  • Meet The Next Gen Of IBMers Helping To Build IBM i
  • Looks Like IBM Is Building A Linux-Like PASE For IBM i After All
  • Will Independent IBM i Clouds Survive PowerVS?
  • Now, IBM Is Jacking Up Hardware Maintenance Prices
  • IBM i PTF Guide, Volume 27, Number 24

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 © 2025 IT Jungle