Midrange News for the IBM i Community


Posted by: Bob Cozzi
Rogue Programmer
Cozzi Productions, Inc.
Chicagoland
RTVDATE (Retrieve and Convert Date) Command
has no ratings.
Published: 22 Jun 2012
Revised: 23 Jan 2013 - 1582 days ago
Last viewed on: 24 May 2017 (3161 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

RTVDATE (Retrieve and Convert Date) Command Published by: Bob Cozzi on 22 Jun 2012 view comments(17)

If you have a second, take a look at the docs for the RTVDATE command in COZTOOLS.

Link to RTVDATE Documentation

Let me know what you think, if it could be useful (I know, I know, if it were 1990...) and if anything else needs to be added to the command itself.

What I've heard is that the top 3rd-party "tool" that folks are using is a CL date conversion tool, so I thought it would be useful to include a more powerful one in COZTOOLS free runtime. Here's a print-key of the CL prompter of this command:

Thanks for the feeback--oh and post your freedback here if you don't mind. Thanks.

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: TFisher
Premium member *
Comment on: RTVDATE (Retrieve and Convert Date) Command
Posted: 4 years 10 months 27 days 13 hours 51 minutes ago

Bob,

This is a useful command.  I have created a function that we can use here in HLLs, the command can be used in CL.

 

About the only differences is that mine also has keywords to allow us to retrieve the next work day (*WORKDAY).  The returned date will not be a weekend or holiday.

 

Here is a question for you...if today was Friday, June 22, 2012 and the command string looked like this 'RTVDATE RTNDATE(&LastThurs) DATE(*JOB *PRV *THU)' then what date would be returned?  6/21 or 6/14?  I would expect a date of 6/14.

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: RTVDATE (Retrieve and Convert Date) Command
Posted: 4 years 10 months 27 days 12 hours 5 minutes ago
Edited: Fri, 29 Jun, 2012 at 09:33:34 (1790 days ago)

The *PRV and *NEXT always return the last or next day where as the value "THIS" returns the next day unless today is that day, then it returns today. So you can make it work either day.

This too has the workday option but there is no calendar support hence, it returns the "next" business day as Mon to Fri.

Posted by: TFisher
Premium member *
Comment on: RTVDATE (Retrieve and Convert Date) Command
Posted: 4 years 10 months 25 days 22 hours 55 minutes ago
Edited: Thu, 28 Jun, 2012 at 06:21:48 (1791 days ago)

So the *PRV and *NEXT keywords mean 'previous work week' and 'next work week' and not 'previous day' and 'next date'.

 

You might want to clarify that in your documentation so people using your command will not that if they want to retrieve date ranges for the previous work week they will know that this will work.  That is, if they retrieve the *PRV *MON and today is Tuesday they will not get yesterday's date, but the date for the Monday prior to yesterday.

 

Also, I wouldn't say that my command or function contain any "calendar support".  All the holidays are calculations.  We have US holiday and Canadian holidays supported (I think this functionality is primarily used by our A/R group and I know that it's used by a process that monitors bank account activities to know that it's a Canadian holiday when we don't receive a file).

 

Something else that mine has is an optional parameter that returns the day of week for the date being retrieved. I also have a date format keyword called *WORD with a parameter for the format to be specified.  This allows any date format, like 'Mmmmmmmmmz ZD, YYYY', to be returned. 

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: RTVDATE (Retrieve and Convert Date) Command
Posted: 4 years 10 months 25 days 20 hours 30 minutes ago
Edited: Fri, 29 Jun, 2012 at 09:33:06 (1790 days ago)

Fish, no. If today is Tuesday, retrieving *PRV wil return yesterday's Monday date.

The final paragraph in my previous Comment was related to the command itself "this command" not to the functionality of the relative date parameter.

And yes, the RTVDATE command also supports "any format". Rather than put *YMD or whatever, you specify any non-asterisk value and it uses that as the map/format. Remember, this is being pulled out of RPG xTools so there's at least 10-years of use behind it.

So you can specify RTNFMT(DDMMMYYYY) and the date is returned as 27JUN2012.

Posted by: TFisher
Premium member *
Comment on: RTVDATE (Retrieve and Convert Date) Command
Posted: 4 years 10 months 25 days 18 hours 17 minutes ago

Sounds cool, remember that I am not familiar with xTools.  Since the functionality I am asking about is is something we commonly use here, and everywhere else I have worked, and since you say RTVDATE has 10-years behind it, I have to assume that the command supports this and your documentation doesn't make this clear and that you are not fully understanding my question.

 

I would expect *PRV to return yesterday's date.  But what I am asking you is that if I want *PRV *MON and today is Tuesday would I still get yesterday's date or last Monday's date (8 days ago)? 

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: RTVDATE (Retrieve and Convert Date) Command
Posted: 4 years 10 months 25 days 17 hours 6 minutes ago

If today is Tuesday and you ask for *PRV *MON it will return yesterday's date.

Posted by: neilrh
Premium member *
Jackson, MI
Comment on: RTVDATE (Retrieve and Convert Date) Command
Posted: 4 years 10 months 25 days 16 hours 48 minutes ago

Sorry the image isn't showing here at work - so I can't tell if this is covered.  Is the returned date based on Today, or can I specify a date to work from.  Say I want to find the first Thursday in July - can I do that with this command, essentially by saying get me the next Thursday from 2012-06-30?

Posted by: TFisher
Premium member *
Comment on: RTVDATE (Retrieve and Convert Date) Command
Posted: 4 years 10 months 25 days 12 hours 22 minutes ago
Edited: Thu, 28 Jun, 2012 at 16:51:35 (1791 days ago)

Okay, then your documentation is fine...but the command would probably not work for most of the companies who would want to retrieve date ranges for weekly reports.

 

You should really consider this: 

1. Have an optional parameter that allows the first day of the work week to be specified (*SUN - *SAT).  Default that parameter to *SUN.

2. Change *PRV and *NEXT when a day of week is specified (you also need a *CURRENT) to work like this:

   A. *PRV will retrieve a date for the specified day for the previous work week.

   B. *CURRENT will retrieve a date for the specified day for the current work week.

   C. *NEXT will retrieve a date for the specified day for the next work week.

 

Posted by: TFisher
Premium member *
Comment on: RTVDATE (Retrieve and Convert Date) Command
Posted: 4 years 10 months 25 days 12 hours 4 minutes ago
Edited: Thu, 28 Jun, 2012 at 17:01:57 (1791 days ago)

Neil,

I think you would have to do some of the work to find the date for the "first Thursday in July".  This command could be used to help you arrive at that date.

 

My ILE function allows this.  By passing in a "base date" of '7/01/2012' instead of the default of '*CURRENT' and a find date of '*FIRST' and '*THU' you would get back the first Thursday of the month of July.  My function supports '*FIRST', '*SECOND', '*THIRD', '*FOURTH', and '*LAST'.  It also supports all days of the week, *WORKDAY, *WEEKDAY, *WEEKEND, and *HOLIDAY (US or Canada based on another parameter).

 

I forgot about this, it also allows me to enter a duration to add or subtract from the base date.  If *WORKDAY is specified then weekends are holidays are omitted, meaning that if today is Friday and you add 1 day then next Monday is the date returned.  If next Monday is a holiday then next Tuesday is returned.  *WEEKDAY does the same thing except that holidays are not omitted and *WEEKEND would omit all week days and always return either a Saturday or Sunday date.

I only mention this to show that what you want can be done in a command (or ILE function) and to try to give you guys some ideas for building the best possible date routine.  -- I just thought about what I just typed...I am not trying to say mine is the "best", only trying to point out what I feel is missing and necessary functionality.  Perhaps the holiday calculations I put in mine was a little overkill since only 4 or 5 programs actually use these calcs, but it was fun doing the research and coming up with the algorithms for each of the holidays. 

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: RTVDATE (Retrieve and Convert Date) Command
Posted: 4 years 10 months 25 days 9 hours 49 minutes ago

Neil, yes it can do that. You pass in a base-date which defaults to "today" if unspecified. Or you can specify one of the optional special values, such as *TODAY, *TOMORROW, *YESTERDAY, *EOM or *BOM  (*BOM = beginning of month).

Posted by: TFisher
Premium member *
Comment on: RTVDATE (Retrieve and Convert Date) Command
Posted: 4 years 10 months 25 days 8 hours 29 minutes ago
Edited: Thu, 28 Jun, 2012 at 20:31:57 (1791 days ago)

Bob,

I don't think you are reading our posts very closely.  Your command will NOT do what Neil is asking...unless *BOM really means get the first Thursday of a given monthSmile

 

I hope this feedback is useful...your command is good, but falls short. 

 

Is the RTNFMT parameter long enough to support day and month names?  Your documentation doesn't show the maximum length, so I thought I would mention that in case it's too short to support LONG date formats (Thursday, June 6, 2012).

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: RTVDATE (Retrieve and Convert Date) Command
Posted: 4 years 10 months 24 days 19 hours 41 minutes ago
Edited: Fri, 29 Jun, 2012 at 09:32:23 (1790 days ago)

Fish, I think I read neil's question clearly and answered it correctly ("yes it can").

RTVDATE RTNDATE(&MYDATE)DATE(&BASEDATE *NEXT *THU)

If &BASEDATE is Neil's  June 30th date, this will return the next thursday date from June 30th, which is July 5th 2012.

The Date Format can be up to 128 characters in length.

Posted by: TFisher
Premium member *
Comment on: RTVDATE (Retrieve and Convert Date) Command
Posted: 4 years 10 months 24 days 19 hours 30 minutes ago

That will return next Thursday's date.  How could one use your command to retrieve the first occurrence of a day?  For example, if I want to retrieve the first Monday in September (Labor Day) how would I do that with this command?

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: RTVDATE (Retrieve and Convert Date) Command
Posted: 4 years 10 months 24 days 19 hours 25 minutes ago
Edited: Fri, 29 Jun, 2012 at 09:31:02 (1790 days ago)

Okay, I didn't realize you had a different question in your mind vs what Neil had asked.

To do what you want, you would code this:

RTVDATE RTNDATE(&MYDATE)DATE(090112 *FIRST *MON)

 

Posted by: neilrh
Premium member *
Jackson, MI
Comment on: RTVDATE (Retrieve and Convert Date) Command
Posted: 4 years 10 months 24 days 16 hours 45 minutes ago

Ah so to get the first Thursday in a July I can do either:

DATE(063012 *next *thu) or DATE(070112 *first *thu)

ok that's neat.

Posted by: TFisher
Premium member *
Comment on: RTVDATE (Retrieve and Convert Date) Command
Posted: 4 years 10 months 24 days 16 hours 39 minutes ago

No, *NEXT will ONLY work if the 'current' date is the week prior to the first Thursday in July.  The correct way is to use the '*FIRST' keyword and a base date that is the first day of the month you want to retrieve.

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: RTVDATE (Retrieve and Convert Date) Command
Posted: 4 years 10 months 24 days 16 hours 1 minutes ago
Edited: Fri, 29 Jun, 2012 at 13:25:11 (1790 days ago)

Neil, Yes, exactly. Or you could say DATE(070112 *THIS *THU) either works (*FIRST or in this context *THIS). In fact, *NEXT would also work in this particular case, but *THIS is probably safer for generic/on-going use.