Tweet
Some SQL stuff that everyone already knows
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