Problem with to_date-function on AIX
in [DB2]
|
Prev: Problem with to_date-function on AIX
Next: Federated Database Using EXISTS Clause with Common Table Expressions
From: Tonkuma on 15 Mar 2010 18:23 Valid format strings are 'YYYY-MM-DD HH24:MI:SS' only, on DB2 for LUW 9.1 or earlier. The format string 'YYYYMMDDHH24MISS' is supported on DB2 for LUW 9.5 or later.
From: guenter on 15 Mar 2010 18:34 On 15 Mrz., 23:54, Norbert Munkel <linger...(a)munkel.net> wrote: > Hi, > > I am facing a little bit of trouble when trying to run the following > statement on some AIX-Instances (All DB2-Versions from 8.2 up to 9.5): > > db2 "select max(to_date(end_time,'YYYYMMDDHH24MISS')) from > table(admin_list_hist()) as histlist" > > I am getting the following error: > > SQL0171N The data type, length or value of argument "2" of routine > "SYSIBM.TO_DATE" is incorrect. SQLSTATE=42815 > > The same statement on Linux returns the right value: > > db2 "select max(to_date(end_time,'YYYYMMDDHH24MISS')) from > table(admin_list_hist()) as histlist" > > 1 > -------------------------- > 2010-03-15-23.10.44.000000 > > 1 record(s) selected. > > What´s wrong with the format string (which should be "argument 2" in > this case)? > > Any hints are welcome! > > cu, > > Norbert HI I have no problems with this statement on 9.5 on aix 5.3 > db2 connect to ***** connect to ***** Database Connection Information Database server = DB2/AIX64 9.5.5 SQL authorization ID = ***** Local database alias = ***** > db2 "select max(to_date(end_time,'YYYYMMDDHH24MISS')) from table(admin_list_hist()) as histlist"b with ur select max(to_date(end_time,'YYYYMMDDHH24MISS')) from table(admin_list_hist()) as histlistb with ur 1 -------------------------- 2010-03-15-23.13.34.000000 1 record(s) selected. mfg guenter
From: Norbert Munkel on 15 Mar 2010 18:42 Thanks. Missed that one... :-( Tonkuma schrieb: > Valid format strings are 'YYYY-MM-DD HH24:MI:SS' only, on DB2 for LUW > 9.1 or earlier. > > The format string 'YYYYMMDDHH24MISS' is supported on DB2 for LUW 9.5 > or later. >
From: Norbert Munkel on 15 Mar 2010 18:55 Anyway: the timeformat-modifier of "export" supports this kind of stuff in all Versions...with "MM" instead of "MI" of course. Like having two kinds of printf-syntax in the same engine. ;-) just my 2 ct´s Norbert Munkel schrieb: > Thanks. Missed that one... :-( > > Tonkuma schrieb: >> Valid format strings are 'YYYY-MM-DD HH24:MI:SS' only, on DB2 for LUW >> 9.1 or earlier. >> >> The format string 'YYYYMMDDHH24MISS' is supported on DB2 for LUW 9.5 >> or later. >>
From: Tonkuma on 15 Mar 2010 18:58
An example without using to_date: ------------------------------ Commands Entered ------------------------------ SELECT MAX( TIMESTAMP( TRANSLATE('YyZz-Mm-Dd-Hh.Nn.Ss', end_time, 'YyZzMmDdHhNnSs') ) ) FROM TABLE(admin_list_hist()) AS histlist; ------------------------------------------------------------------------------ 1 -------------------------- 2009-12-24-02.42.17.000000 1 record(s) selected. |