- Why do I get an error when I move zero to a date?
- How do I validate that the date in the Date data type is a valid date?
- If I have an ISO date and an USA date do I have to convert them to the same date format before I can compare them?
- Why don't you initialize you dates using UDATE or *DATE?
- Why do I get an error message when I move a date from one Date data type to another
Why do I get an error when I move zero to a date?
Date data types can only contain valid dates. Zero is not a valid date, therefore, if you try and move it into a Date data type you will get a RNQ0112 error, "Date, Time or Timestamp value is not valid".
If you want to move the lowest value to a date field try:
ISO_date = *loval ; |
In this example: ISO_date = 0001-01-01
The same will apply if you want to move all 9s to a date, as that is not a valid date. Replace that with moving *HIVAL into your Date data type.
You might this section from Tuesday useful, see here.
How do I validate that the date in the Date data type is a valid date?
You do not have to. If a value is in a Date data type then it has to be a valid date.
After publishing this post I received this comment from Jon Paris:
If the date field is in a DS then it is perfectly possible for it to contain an invalid date. Trust me - I've seen it happen.
He is correct. If the Date data type is a subfield in a data structure it can contain an invalid date. I have seen the same with numeric subfields.
I should have said: With standalone Date data type variables you do not have to.
If I have an ISO date and an USA date do I have to convert them to the same date format before I can compare them?
You can compare any formatted Date data type with another in a different format. For example:
ISO_date = d'2015-10-22' ;
MDY_date = d'2015-10-22' ;
if (ISO_date = MDY_date) ;
dsply (%char(ISO_date:*iso-) + ' = ' + %char(MDY_date:*usa/)) ;
endif ;
|
Will display:
DSPLY 2015-10-22 = 10/22/15 |
Why don't you initialize you dates using UDATE or *DATE?
UDATE and *DATE contain the job date. As the environment I work in is 24 hours a day/6 days a week I am wary of using any "date" that is not the current/system date. If a job starts before midnight and continues into the next day UDATE and *DATE will contain the previous day's date.
I advise you all to use the %DATE built in function to get today's date. If you use it with no parameter it returns the current date:
ISO_date = %date() ; |
If you really want to have the job date then I do this in the definition specifications:
dcl-s ISO_date1 date(*iso) inz(*job) ; DName+++++++++++ETDsFrom+++To/L+++IDc.Keywords++++++++++++++ D ISO_date2 S D datfmt(*iso) inz(*job) |
You can learn more about initializing with special values here.
Why do I get an error message when I move a date from one Date data type to another
When you get an error RNQ0114, "The year portion of a Date or Timestamp value is not in the correct range", moving one Date data type to another it is all about how they are formatted.
Each date format can only contain a range of years. If move a date with a year that is outside of that range you get an error. These are the accepted range of years by Date data type:
| Date formats | Years |
| *DMY *MDY *YMD | 1940 – 2039 |
| *CYMD *CMDY *CDMY | 1900 – 2899 |
| *ISO *USA *EUR *JIS | 0001 – 99999 |
I have seen this happen the most when an *ISO Date data type containing its default value, 0001-01-01, is moved to a *MDY. Year 0001 just does not go into the range 1940 - 2039.
Learn more about date formats here.
Others in this series include:
"How do I validate that the date in the Date data type is a valid date?"
ReplyDeleteThe answer to this needs a rider Simon. If the date field is in a DS then it is perfectly possible for it to contain an invalid date. Trust me - I've seen it happen.
Yes, it may happen if you overlay a Date subfield with another field or if you clear data structure. But with simple assignment opcode like
Deleteeval datastructure.datefield = ....
you cannot store invalid date value.
Very true. I have added that caveat to the relevant answer. See here.
ReplyDeleteThere is a case for using job date. If you need to run something as if its in the past then if the RPG uses job date then its easy. If you have an advanced pricing function for instance that has different prices and discounts that are only effective in certain date ranges and you want to reproduce a problem that happened in live some time ago.
ReplyDelete"How do I validate that the date in the Date data type is a valid date?"
ReplyDeleteI did not see anyone answer this question. If the date type field is in a data structure and you want to test for a valid date, I would move the DS field to a date type work field within a MONITOR structure.
I really intercommunicated what I meant with this question. It was about how to validate a stand alone Date data type variable, which is not part of a data structure. Perhaps this would be a field from a data file or a display file.
Deleteto validate field with the date datatype, try this
Deleted date_from_db s d inz
/free
test(e) date_from_db;
if %error() or date_from_db = *loval;
dsply 'date is invalid';
else;
dsply 'date is valid';
endif;
/end-free
Give this a try... and let me know...
Why is there no BETWEEN operation?
ReplyDeleteIt would be nice to write
If CheckInDate Not Between CheckOutDate and OverDueDate;
chargeOverdue();
EndIf;
I'd like to expand on Question #5's solution for receiving RNQ0114. I ran into the problem while performing an embedded SQL Select ... where date_column = :iso_date_variable; While scouring the web I found the answer to my issue which also gave a great explanation of the cause.
ReplyDeleteA date column in an SQL defined table does not have a date format. When accessing SQL Tables the date format used by default comes from the current environment/job. In embedded SQL additional variables are created and the date format for these variables is determined by the OPTION DATFMT in the compile command. The default value for this option is *JOB which means the job date format normally has a 2 digit year (*MDY). Thus, the RPG program crashes as soon as a date value before 01/01/1940 or after 12/31/2039 is moved (under the covers) into these additional date fields.
To avoid these problems with date host variables, you could change the compile option DATFMT from *JOB to *ISO, or a better solution is to add a SET OPTION Statement with DATFMT = *ISO in the source code before ALL other SQL statements. The SET OPTION will override the compile options.
Exec SQL SET OPTION DATFMT=*ISO;