Get the Day of the Week, Full Text Date Values, and More Within RPG Using SQL

[T.Snyder]

** This thread discusses the article: Get the Day of the Week, Full Text Date Values, and More Within RPG Using SQL (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=16365) **
Thanks tstephens55 and R.Khoury for some great alternatives!<br/>
&nbsp;<br/>
<b>tstephens55</b><br/>
I don&lsquo;t see any reason why you couldn&lsquo;t use <code>values</code> instead. I&lsquo;ve used values in statements before, but never like that. I think it&lsquo;s a great alternative and I&lsquo;ll be using it. Thanks!<br/>
&nbsp;<br/>
<b>R.Khoury</b><br/>
You are absolutely right. For programmers that are used to using embedded SQL it would be easier to just code it in the statement. For situations where you&lsquo;re in a staff that is early in adoption of embedded SQL then using procedures could let programmers who do not feel comfortable with embedded SQL programs to access the functionality without dealing with the details or changes in the way they compile. And if for some reason you put your functions in a service program it could be like a code repository for code snippets that embedded SQL programmers, like yourself, could use to copy and paste juicy pieces of code.<br/>
&nbsp;<br/>
Thanks a lot for the posts! Happy Coding!<br/>
Tom

[T.Snyder]

** This thread discusses the article: Get the Day of the Week, Full Text Date Values, and More Within RPG Using SQL (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=16365) **
And Thank You Chris! You&lsquo;re so right, that&lsquo;s what it&lsquo;s all about. Thanks everyone for the alternatives. You took the article a step beyond what I put out there and taught me some new tricks. Thank You!<br/>
&nbsp;<br/>
Thanks for introducing me to <code>set</code> R.Khoury. Nice use of it Chris!<br/>
&nbsp;<br/>
Have a great day!<br/>
Tom<br/>

[tstephens55]

** This thread discusses the article: Get the Day of the Week, Full Text Date Values, and More Within RPG Using SQL (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=16365) **

Is there a reason you would not have used the SQL value retrieval instead of referencing SYSIBM/SYSDUMMY1?
Exp. exec sql values week(:argDate) into :svWoy;

[RingerSoftware]

** This thread discusses the article: Get the Day of the Week, Full Text Date Values, and More Within RPG Using SQL (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=16365) **
Tom, thanks for taking the time to write your articles. I already knew about the using SQL SET but sometimes your articles teach me something new and then at times you get to learn something new too. We all improve our skills.

Exec SQL SET : iUserID = Upper(: P0iUserID ) ;

Chris Ringer

[RingerSoftware]

** This thread discusses the article: Get the Day of the Week, Full Text Date Values, and More Within RPG Using SQL (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=16365) **
Actually, I did learn from R.Khoury that one SET statement can do multiple sets, separating each SQL statement by a comma.

[R.Khoury]

** This thread discusses the article: Get the Day of the Week, Full Text Date Values, and More Within RPG Using SQL (http://www.mcpressonline.com/index.php?option=com_content&view=article&id=16365) **
Tom, I think a simpler way to take advantage of SQL functions is to make your program SQLRPGLE and embed the statement directly in it. Also, you don't have to use SYSDUMMY1 to get the result. Yes, it's a very useful table for certain times but unnecessary here. My example just uses the SET statement - as you can see, you can put several together in one statement. I think this is simpler than invoking a service pgm to replace essentially one line of code.

/free
mydate = %date();

EXEC SQL
set :weekDay = dayOfWeek(:mydate),
:dayOfYr = dayOfYear(:mydate),
:weekNum1 = week(:mydate),
:weekNum2 = week_iso(:mydate);

dsply mydate;
dsply weekDay;
dsply dayOfYr;
dsply weeknum1;
dsply weekNum2;
*inLR = *on;
/end-free

Russ K