![](/accounts/common/images/logo.png)
Scalar Functions & Date/Time Calculation — Birgitta Hauser
-
Register
- Member - Free!
- 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
![Speaker image](https://70ecbb36cf5afa131dcc-6f15f2badde623b67dc4ad2e21c9f77b.ssl.cf2.rackcdn.com/common_b68a43a96a5f8b8f14031c8c43783f76.jpg)
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.
She received the John Earl Speaker Scholarship Award in 2015, the Al Barsa Memorial Scholarship Award in 2018, and was named an IBM Champion in 2021.
Key:
![](/images/product-status-icons/complete-small.png)
![](/images/product-status-icons/next-small.png)
![](/images/product-status-icons/disabled-small.png)
![](/images/product-status-icons/optional-small.png)
![](/images/product-status-icons/locked-small.png)