zOs/SQL/DATE

with d (d) as (
           select current timestamp                from sysibm.sysDummy1
 union all select timestamp('2013-01-01-00.00.00') from sysibm.sysDummy1
 union all select timestamp('2012-12-31-00.00.00') from sysibm.sysDummy1
 union all select timestamp('2012-01-01-00.00.00') from sysibm.sysDummy1
 union all select timestamp('2012-01-01-00.00.00') from sysibm.sysDummy1
 union all select timestamp('2012-01-01-23.59.00') from sysibm.sysDummy1
 union all select timestamp('2011-12-31-00.00.00') from sysibm.sysDummy1
 union all select timestamp('2011-01-01-00.00.00') from sysibm.sysDummy1
 union all select timestamp('2010-12-31-00.00.00') from sysibm.sysDummy1
 union all select timestamp('2010-01-01-00.00.00') from sysibm.sysDummy1
 union all select timestamp('2009-12-31-00.00.00') from sysibm.sysDummy1
)
select d
  , smallInt(dayOfWeek(d)) "dayWe"
  , smallInt(dayOfWeek_Iso(d)) "dayWI"
  , smallInt(dayOfWeek(d)) "dayWe"
  , date(trunc_timestamp(d, 'day')) "trunc(day)"
  , date(trunc_timestamp(d, 'iw')) iw
  , date(trunc_timestamp(d, 'ww')) ww
  , dayOfWeek(d) "dayWe"
  , date(trunc_timestamp(d - dayOfWeek(d) days, 'day')) dw
from d
;;,
select visum2_timestamp,
       char(date(visum2_timestamp), iso),
       translate('1234679a', char(date(visum2_timestamp), iso)
            , '123456789a')
    from OA1T.VKS835A1V
    fetch first 1000 rows only
 ;;;
select char(current date, iso),
       translate('1234679a', char(current timestamp, iso), '123456789a')
    from sysibm.sysDummy1
;;;
select right(digits(year(current date)), 4)
         || right(digits(month(current date)), 2)
         || right(digits(day(current date)), 2)
    from sysibm.sysDummy1
;
select strip(char(year(current date)))
         || right('0' || strip(char(month(current date))), 2)
         || right('0' || strip(char(day(current date))), 2)
    from sysibm.sysDummy1
;
select replace(char(current date, iso), '-', '') "iso rep"
    from sysibm.sysDummy1
;
select current date "def",
       char(current date) "char",
       char(current date, iso) "iso",
       char(current date, eur) "eur",
       char(current date, usa) "usa",
       char(current date, jis) "jis",
       char(current date, local) "local",
       replace(char(current date, iso), '-', '') "iso rep",
       right(digits(year(current date)), 4)
         || right(digits(month(current date)), 2)
         || right(digits(day(current date)), 2)
    from sysibm.sysDummy1
;
x
delete from                    LDAP101A.DIR_DESC         ;
delete from                    LDAP101A.DIR_ENTRY        ;
delete from                    LDAP101A.DIR_LONGATTR     ;
commit;
delete from                    LDAP101A.DIR_LONGENTRY    ;
delete from                    LDAP101A.DIR_ATTRID       ;
delete from                    LDAP101A.DIR_CACHE        ;
delete from                    LDAP101A.DIR_MISC         ;
commit;
delete from                    LDAP101A.DIR_PROGRESS     ;
delete from                    LDAP101A.DIR_REGISTER     ;
delete from                    LDAP101A.DIR_CHANGE       ;
delete from                    LDAP101A.DIR_LONGCHANGE   ;
delete from                    LDAP101A.DIR_SEARCH       ;
commit;
x
delete from DGDB9998.TWK001A;
INSERT INTO DGDB9998.TWK001A (WK001NAME, WK001NUM, WK001DA, WK001T1)
      VALUES  ('eur', 999, '25.12.2007', 'a');
INSERT INTO DGDB9998.TWK001A (WK001NAME, WK001NUM, WK001DA, WK001T1)
      VALUES  ('iso', 999, '2007-12-26', 'a');
INSERT INTO DGDB9998.TWK001A (WK001NAME, WK001NUM, WK001DA, WK001T1)
      VALUES  ('usa', 999, '12/27/2007', 'a');
SELECT * FROM DGDB9998.TWK001A;
rollback