How Do I Format Date Literals? Let Me Count The Ways

Published: 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