Code:
H dftactgrp( *no ) OPTION(*NODEBUGIO) //--------------------------------------------- // using SQL date and time functions // IBM DB2 *ISO *USA *JIS *EUR date formats // SQL values and set options //--------------------------------------------- d Mystring s 512 varying d MyStamp s z d MyIsoDate s d d SQLIso s 10 d SQLUsa s 10 d SQLEur s 10 d SQLJis s 10 d SQLUsaDate s d datfmt(*USA) d WorkIsoDate s d /Free exec sql set option commit=*none,datfmt=*iso; //--------------------------------------------- // M A I N L I N E //--------------------------------------------- MyString = 'This is a upper case test'; exec sql select upper(:MyString) into :MyString from sysibm/sysdummy1; // after processed string will be: // 'THIS IS A UPPER CASE TEST' // retrieve a timestamp in SQL exec sql select (CURRENT TIMESTAMP) into :MyStamp from sysibm/sysdummy1; // after processed MyStamp will be something like: // '2012-01-04-08.05.08.811000' // retrieve a date in 'YYYY /MM / DD' format exec sql select (CURRENT_DATE) into :MyIsoDate from sysibm/sysdummy1; // some SQL data math exec sql select (CURRENT_DATE + 3 YEARS + 2 MONTHS + 15 DAYS) into :MyIsoDate from sysibm/sysdummy1; // select date in character multiple formats exec sql select char(CURRENT_DATE,ISO), char(CURRENT_DATE,USA), char(CURRENT_DATE,EUR), char(CURRENT_DATE,JIS) into :SQLiso, :SQLusa, :SQLeur, :SQLjis from sysibm/sysdummy1; // select date in usa format *note datfmt on variable definition exec sql select (CURRENT_DATE) into :SQLUSADate from sysibm/sysdummy1; //output : '01/04/2012' // now that I've posted this all in selects using sysdummy try using // Values or set exec sql values(CURRENT_DATE - 30 DAYS) into :WorkIsoDate; exec sql Set :WorkIsoDate = Current_Date - 30 Days; *inlr = *On; /End-Free