Scalar Functions & Date/Time Calculation — Birgitta Hauser

Scalar Functions

Scalar Functions for String handling (UPPER, LOWER, TRIM, SUBSTR, TRANSLATE, LOCATE)

Solving Problems:

Sorting Character Columns (case insensitive – UPPER/LOWER)
Determining rows with invalid numeric characters in a character field (TRANSLATE)
Converting or Replacing invalid characters (for example due to a wrong CCSID – TRANSLATE, REPLACE)
Left-/Right adjusting texts (TRIM, SPACE, REPEAT, RIGHT, LPAD, RPAD)
Splitting Columns (FirstName, LastName separated by a comma – SUBSTR, LOCATE)

Pitfalls when calculating with Integer Values/Columns (especially when dividing integer numbers)
Rounding Functions

ROUND – Rounding to decimal positions, rounding to 100, 1000 etc.
CEILING/FLOOR Rounding up or down to the next integer.

Example: Packing pallets – 20 Boxes on a pallet – for 102 Boxes 6 pallets and not 5 are needed

Converting between Data Types (CAST)

Date & Time Calculation

Date/Time/Timestamps 3 different data types
Converting numeric Date/Time values into real date (and vice versa)
How to convert a date like Monday 11-SEP-23 19:43 into a real timestamp?
Adding time units (Days, Hours, …) to Dates/Times/Timestamps

Adding/subtracting the same number of months may not return the start date

Subtracting two date/time values:

Why is the result ‘2023-09-10’ – ‘2023-08-08’ 102?
How to get the difference in Minutes or Days?

Date/Time Functions: DayName, Week_ISO, Last_Day, First_Day, Next_Day
Dimension Table

Birgitta Hauser

Independent Consultant

Birgitta Hauser graduated with a business economics diploma and started programming on the AS/400 in 1992. She is an experienced RPG programmer as well as a database and software engineer, focusing on IBM i application and database modernization. Today, she is an independent consultant on application and database modernization on IBM i and Db2 for i.

Birgitta also works in education as a trainer for RPG and SQL developers. Since 2002, she has been a frequent speaker at COMMON user groups meetings and other IBM i and Power conferences in Germany, other European countries, the United States, and Canada. In addition, she is co-author of two IBM Redbooks and has also authored several articles and papers focusing on RPG and SQL for the ITP Verlag (a German publisher), IT Jungle Guru, and IBM DeveloperWorks.