Selecting a Range of Date Values in SQL When the Date Is Stored as a Numeric Value

Troubleshooting

Problem

For SQL to select a date range, the value must be stored as a date or be converted to a date.

Resolving The Problem

For SQL to select a date range, the value must be stored as a date or be converted to a date. If the date is stored in a character field, it can be converted to a date using substring, concatenate, and the DATE SQL function. If it is stored in a numeric field (for example, packed or zoned), it must first be converted to character before string operations can be performed on the value. In addition, the date format of the date literal specified in the where condition must match the date format of the SQL session.

Following is an example of how to convert a numeric value to a date data type in an SQL WHERE clause:

select zonedate6

from datefile

 where date(substr(digits(zonedate6),1,2) || '/' ||

            substr(digits(zonedate6),3,2) || '/' ||

            substr(digits(zonedate6),5,2))

            between '01/01/98'

and '12/31/00'

[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"Db2 for i","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"Version Independent","Edition":"","Line of Business":{"code":"LOB57","label":"Power"}}]

Historical Number

N1017122

Was this topic helpful?

Document Information

More support for:
IBM i

Software version:
Version Independent

Operating system(s):
IBM i

Document number:
706645

Modified date:
12 March 2020

UID

nas8N1017122

IBM Support

Tips