• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • How Do I Format Date Literals? Let Me Count The Ways

    May 2, 2012 Hey, Ted

    I have a strange SQL problem. I have an SQL DELETE command that works perfectly in interactive SQL. But when I use Run SQL Statement (RUNSQLSTM) to execute it, it does not find any rows to delete.

    –Jabir

    Strange, indeed. I gave Jabir some suggestions, but none of them helped. Jabir found his problem himself. In one of those environments (he didn’t say which), the date format was *MDY, while the date format of the other one was *DMY. SQL was interpreting a date value in two different ways. It’s fortunate that Jabir did not test with a date that has the same number for month and day (such as January 1), otherwise he might have installed the error into production.

    To the database manager, a date is a serial number. The value assigned to a particular date is of no importance to us. All we need to know is that the serial number of today is one more than the serial number of yesterday’s date and one less than that of tomorrow’s date.

    However, dates are something different to humans. Dates are a combination of year, month, and day. The database allows humans to enter dates as strings, and different interfaces show the same data in different human-readable formats. But how a date is displayed has nothing to do with how the database manager stores it.

    According to the SQL reference manual, you may specify a date literal in any of these format:

    • The job’s default date format
    • The ANSI/ISO SQL standard date format (DATE ‘yyyy-mm-dd’)
    • Any of the IBM SQL standard date formats (ISO/JIS, EUR, USA)
    • Unformatted Julian format (‘yyyyddd’)

    For example, assume that the default date format of a job is *MDY. You may use any of the following SELECT statements to retrieve January 3, 2010, sales orders.

    Job date format:

    SELECT * FROM SALESORDH WHERE ORDERDATE = '01/03/10'
    

    ANSI/ISO SQL standard:

    SELECT * FROM SALESORDH WHERE ORDERDATE = DATE '2010-01-03'
    

    IBM standard ISO and JIS formats:

    SELECT * FROM SALESORDH WHERE ORDERDATE = '2010-01-03'
    

    IBM standard USA format:

    SELECT * FROM SALESORDH WHERE ORDERDATE = '01/03/2010'
    

    IBM standard EUR format:

    SELECT * FROM SALESORDH WHERE ORDERDATE = '03.01.2010'
    

    Unformatted Julian format:

    SELECT * FROM SALESORDH WHERE ORDERDATE = '2010003'
    

    As I see it, you can never go wrong with good ol’ IBM standard ISO format.

    –Ted



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    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

    Sponsored Links

    Tembo Application Generation:  Reclaim your heritage with ADSERO OPTIMA!
    Tributary Systems:  Storage Director, a disk-to-disk-to-tape solution. Download the case study.
    COMMON:  Join us at the 2012 Conference & Expo, May 6 - 9 in Anaheim, CA

    IT Jungle Store Top Book Picks

    BACK IN STOCK: Easy Steps to Internet Programming for System i: List Price, $49.95

    The iSeries Express Web Implementer's Guide: List Price, $49.95
    The iSeries Pocket Database Guide: List Price, $59
    The iSeries Pocket SQL Guide: List Price, $59
    The iSeries Pocket WebFacing Primer: List Price, $39
    Migrating to WebSphere Express for iSeries: List Price, $49
    Getting Started with WebSphere Express for iSeries: List Price, $49
    The All-Everything Operating System: List Price, $35
    The Best Joomla! Tutorial Ever!: List Price, $19.95

    Island Pacific Completes ERP Upgrade for Clothing Retailer Apps And IBM i Evangelism

    Leave a Reply Cancel reply

Volume 12, Number 12 -- May 2, 2012
THIS ISSUE SPONSORED BY:

Infor
WorksRight Software
American Top Tools

Table of Contents

  • CL Error-Handling With APIs
  • How Do I Format Date Literals? Let Me Count The Ways
  • Admin Alert: Secrets Of The IBM i IPL Parameters

Content archive

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

Recent Posts

  • How Committed Is Big Blue To The IBM Cloud?
  • Immutable Copies Are Only As Good As Your Validation
  • Guru: IBM i *USRPRF Security
  • ERP Transitions Loom for SAP on IBM i Customers
  • Inflation Pumps Up Global IT Spending, Supply Chain Deflates It
  • COMMON Set for First Annual Conference in Three Years
  • API Operations Management for Safe, Powerful, and High Performance APIs
  • What’s New in IBM i Services and Networking
  • Four Hundred Monitor, May 18
  • IBM i PTF Guide, Volume 24, Number 20

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.