Home > IT Answers > Storage > SQL dynamic date calc.
DanTheDane
2,540 pts.
 SQL dynamic date calc.
IBM 9407 M15, SQL, SQL Calculations, SQL Dynamic Date Calculation
How shall the WHERE clause look for this challenge:

Pseudocode: select all records from file where datefield (ccyymmdd 8.0) shall be greater than todays month minus 36 months.

As you can see this may be expressed as [em]rolling 36 full months plus current month[/em].

Any help will be apreciated.

Dan



Software/Hardware used:
9407/M15 V6R1M0
ASKED: September 4, 2009  4:45 PM
UPDATED: September 7, 2009  1:19 PM
RELATED QUESTIONS
Answer Wiki:
Something like this??? select * from myFile where myDt80 > substring(char (current date - 36 months ,iso),1,4) || substring(char (current date - 36 months ,iso),6,2) || substring(char (current date - 36 months ,iso),9,2 ) Phil ---------------------- Thanks for the feeback Phil
Last Wiki Answer Submitted:  September 7, 2009  1:19 pm  by  philpl1jb   44,110 pts.
All Answer Wiki Contributors:  philpl1jb   44,110 pts.
To see all answers submitted to the Answer Wiki: View Answer History.


RSS - Discussions Help
Discuss This Question:
_ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _ _

 

Thanks Phil

Syntax works fine, BUT we’ll need to replace your last line with ’00′ to include transactions from 1.st of the month (ie full month).
Solution therefor is:

substring(char (current date - 36 months ,iso),1,4) || 
substring(char (current date - 36 months ,iso),6,2) ||  '00'

Dan

DanTheDane
 2,540 pts.