How To
Summary
The RR and RRRR format elements can be used to alter how a specification for a year is to be interpreted by adjusting the value to produce a 2-digit or a 4-digit value depending on the leftmost two digits of the current year.
Objective
Provide tips to make to_date() and timestamp_format() sacalar functions easier to use.
Environment
IBM i
Steps
When using the to_date() and timestamp() sql scalar functions there are some useful tips to keep in mind in regards to the "year".
Considering year 2024 as the actual year, let's look at the following queries results:
select to_date('10/01/95', 'MM/DD/YY') from sysibm.sysdummy1;
2095-10-01 00:00:00.000000
select timestamp_format('10/01/95','MM/DD/YY') from sysibm.sysdummy1;
2095-10-01 00:00:00.000000
Is year 2095 right or wrong? Well, it will depend on the meaning of this piece of information. It might be right for a statistical scientific prediction but wrong if it refers to an employee birthdate. Then, how can we get the right birthday year for the employee?
We can use RR and RRRR format elements instead of YY and YYYY in the format string as indicated in the following table:
Here are some example:
select to_date('10/01/95', 'MM/DD/RR') from sysibm.sysdummy1;
1995-10-01 00:00:00.000000
select timestamp_format('10/01/95','MM/DD/RR') from sysibm.sysdummy1;
1995-10-01 00:00:00.000000
select to_date('10/01/85', 'MM/DD/RRRR') from sysibm.sysdummy1;
1985-10-01 00:00:00.000000
select timestamp_format('10/01/85','MM/DD/RRRR') from sysibm.sysdummy1;
1985-10-01 00:00:00.000000
So for our employee's birthdate, year 1995 makes sense.
Remember that the RR and RRRR format elements can be used to alter how a specification for a year is to be interpreted by adjusting the value to produce a 2-digit or a 4-digit value depending on the leftmost two digits of the current year according to the following table:
For example, for the current year is 2024, '86' with format 'RR' means 1986, but if the current year is 2052, it means 2086.
Related Information
Document Location
United States
Worldwide
[{"Type":"MASTER","Line of Business":{"code":"LOB68","label":"Power HW"},"Business Unit":{"code":"BU070","label":"IBM Infrastructure"},"Product":{"code":"SWG60","label":"IBM i"},"ARM Category":[{"code":"a8m0z0000001i4eAAA","label":"IBM i Db2-\u003ESQL Examples \/ DB Examples \/ Misc how to"}],"ARM Case Number":"TS016161506","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions"}]
Product Synonym
IBM i; IBM db2 for i; IBM i database
Was this topic helpful?
Document Information
Modified date:
07 May 2024
UID
ibm17150399