01 - Programming (EN)01a - RPG (EN)

Dates and times: conversion between different data types

Last Updated on 5 April 2020 by Roberto De Pedrini

Why use the Date and Time data type

The Date data type was introduced in V2R3 (ie roughly in 1994), but still today unfortunately it seems an unknown and “dangerous” data type to use.

A little by tradition and a bit by laziness the dates in the DB2 tables are nothing more than numbers written in the most varied formats entirely managed by the application logic in the programs
. But being precisely nothing more than numeric fields the DB2 ignores completely that the content of the field is a date. The field is a surrogate for a date. So all the effort to manage dates correctly is delegated to the programmer’s skills for nothing by exploiting the potential of DB2.

Converting from date to other data types

There are numerous different methods for working with dates: operating codes and RPG functions, SQL functions, APIs, operating system commands: at least 98 methods!

The requirement to be able to use this treasure trove of operating codes and functions is that the variables or fields are of the Date and Time type and not of the surrogates.

It is therefore important to be able to convert a variable or field from the alphanumeric or numeric data type to the Date and Time data type and vice versa.

From date type to alphanumeric type

The% char function converts a date, time or timestamp into an alphanumeric
field% char (date | time | timestamp
{: format}). If the first parameter is a constant, the conversion is performed at compile
time. The second parameter represents the date format, time or timestamp returned. Separators are included in the result unless 0 is added to the format (e.g. * Iso0).

For example:

 dcl-s DataAlfa char (10);
 dcl-s MiaData date inz ('2019-08-29');

 DataAlfa =% char (MiaData: * ISO0); // 20190829 

* iso0 indicates that the format will be YYYYMMDD without separators. Instead * iso would provide for the use of separators.

ATTENTION: an example is written in the ILE RPG reference manual in the chapter of the function% char to convert a timestamp field into alphanumeric without separators using the keyword * iso & in the second parameter; actually you have to use the keyword * iso0.

From date type to numeric type

The% dec function converts a date into a numeric field.

% dec (date time or timestamp expression {: format})

The format parameter is available from V5R3.

For example:

dcl-s yyyymmdd packed (8);
dcl-s ddmmyy packed (6);
dcl-s MiaData date (* USA) inz (d'2019-10-20 ');

yyyymmdd =% dec (MiaData: * iso);
ddmmyy =% dec (MiaData: * dmy); 

You can also convert a date to a numeric field using the% char and% uns functions. For example:

 yyyymmdd =% uns (% char (MiaData: * iso0)); 

The SQL varchar_format function can also come in handy. P.es .:

  - from date to 8-digit numeric field
dec (varchar_format (current date), 'YYYYMMDD'), 8)
- from date to 7-digit numeric field
dec (varchar_format (current date), 'YYYYMMDD'), 8) - 19000000 

The SQL dec function also allows you to convert from a date / time to a numeric field. P.es .:

dec (MyDate)
dec (MyTime)
dec (MyTimestamp)
dec (MyTimestamp, 26, 12)
dec (dates (MyTimestamp))
dec (time (MyTimestamp), 6, 0) 

Conversion from other type to date / time / timestamp

The% date,% time,% timestamp functions convert a variable to date, time or timestamp. Introduced in V5R1 they replace the operating codes MOVE, MOVEL.

%DATE

 {% Date (expression {: date-format})} 

If the first parameter is not specified, the current system date is returned (N.B. NOT the job date as it would return * DATE). The sec
ond parameter specifies the format of the input date; if not specified it is assumed to be * iso. If the first parameter is a timestamp, or * DATE or UDATE the second parameter is not necessary.
The returned date is always in * iso format.

% TIME

 % Time {(expression {: time-format})} 

If the first parameter is not specified, the current system time is returned. The
second parameter specifies the input time format; if not specified it is assumed to be * iso. If the first parameter is a timestamp, the second parameter is not necessary.
The returned time is always in * iso format.

% TIMESTAMP

% timestamp {(char-num-expression {: * ISO | * ISO0: {fractional-seconds}})}
% timestamp {(date-timestamp-expression {: fractional-seconds})} 

If the first parameter is not specified or set to * sys, the current system timestamp is returned (with only the first 3 digits of the fractional portion of the seconds) .If the firs
t parameter is alphanumeric, the format can be specified in the second parameter .If the
first parameter is numeric the only format allowed is * iso. T
he returned timestamp is always in * iso format.

SQL way

The previous functions are excellent in an RPG program, but if you need to convert to an SQL statement? Nothing prevent
s you from creating a scalar SQL function that with the SQL / PL language or by calling an RPG program does the conv
ersion. example of conversion from YYYYMMDD format (8-digit packed field) to a data data type. The example can then be adapted to carry out the conversion with different formats or improved to manage all possible cases with a single function. The
function we will create is based on the SQL date function which returns a field of type data from a value ( numeric or alphanumeric) which represents a date or timestamp.

SQL statement to create the cvtDate function:

create or replace function CVTDATE (InpData dec (8, 0))
 returns date
 language sql
 contains sql
 no external action
 global deterministic
 return null on null input
 not fenced
 return date (left (char (InpData), 4) concat '-' concat substr (char (InpData), 5, 2) concat '-' concat substr (char (InpData), 7, 2)); 

Other useful SQL functions for converting the date / time / timestamp type: time, timestamp, timestamp_iso, timestamp_format.

The time function returns a time data type of a value that represents an hour or timestamp. If the value is a date time, it returns midnight as the time.

The timestamp function returns a timestamp data type of a value that represents a date or timestamp. Only the first parameter can be specified as a representation of a date value or timestamp and possibly an integer is specified in the second parameter to indicate the precision of the fractional portion of the timestamp. Otherwise, a value representing a date can be specified in the first parameter and a value representing an hour in the second parameter.

Similar to the previous one is the function timestamp_iso which however can only receive a parameter that represents a date, time or timestamp. If you specify a value representing an hour, the given portion is set according to the CURRENT DATE register.

The timestamp_format function (or the equivalents to_date, to_timestamp) converts a string_expression string with format format_string to a timestamp:

 timestamp_format (string_expression, format_string, precision-constant) 

The separators allowed in format_string are: -,., /, ,,,;,:, blank. T
he elements of the format specified in format_string are summarized in the tab. 53 (p. 619) of the SQL reference 7.3 manual. Some examples:

values timestamp_format ('1999-12-31 23:59:59', 'YYYY-MM-DD HH24: MI: SS');
values timestamp_format ('15 / 12/98 13:48 ',' DD / MM / RRRR HH24: MI ');
values timestamp_format ('9-3-2004 8:02', 'DD / MM / RRRR HH24: MI');
values timestamp_format ('190718', 'YYMMDD');
values timestamp_format ('2015-09-04 13:00:01', 'YYYY-MM-DD HH24: MI: SS');
values timestamp_format ('September: 2015: 12 01:00:13', 'Month: YYYY: DD SS: MI: HH24');
values timestamp_format ('Nov: 15: 03 15', 'Mon: RR: DD HH24');
... where timestamp_format (char (DataPacked8), 'YYYYMMDD') between current date and (current date + 30 days)
... where timestamp_format (char (DataPacked7 + 19000000), 'YYYYMMDD') between current date and (current date + 30 days) 

Date conversion table

Another method of “converting” a date stored in DB2 into a field with an inappropriate data type is to use a date conversion table. It is a “calendar” t
able that contains a record for each day in a specific range of dates. Each record contains the s
ame date represented in different ways (Julian date, “true” date, day of the week, YYYYMMDD date, DDMMYY date, excel …). It is documented
in the redbook SG24-7214-01 “Getting Started with DB2 Web Query for i ”(appendix B p. 542-552).

The logic of use of this conversion table is to join it with the table containing the “legacy” date through the most appropriate field (eg. for a legacy data stored in a packed field 8.0 in YYMMDD format use DC_YYMD_DEC) and then use the “true” DC_DATE date field in the rest of the query.

100% guaranteed that this technique offers better performance than any conversion formula, especially if the field is used in the where clause of an SQL statement.

Read the entire article and many others on the site https://www.markonetools.it

Related Posts
DB2 for i SQL – String Manipulation – POSSTR-LOCATE-LOCATE_IN_STRING (EN)

Introduction Often, in our applications, we need to work with text strings, and DB2 SQL can come in very useful Read more

DB2 for i – FAQ & Howtos (EN)

DB2 Database and SQL ... maybe the most important things on IBM i platform: here's a collection of FAQs, tips Read more

IBM i 7.4 Announcement (En)

Comes directly with the Easter egg this IBM announcement for the news of the IBM i 7.4 version, iNext version Read more

Generated Always Columns (EN)

Introduction "Generated Always Column": are columns, table fields, filled by DB2 engine: something like columns with a default value but Read more

About author

IBM i's mentor IBM Champion 2021 for Power Systems

Leave a Reply

Your email address will not be published. Required fields are marked *