db2 date format problem

ashrash · #1 (**permalink**) 08-28-09, 05:24

hi
everyone

i m new to this website.after long searching i found this site is useful.i have a problem in db2

i want to the date from db2 in this format dd-mon-yy

can anyone pls tell me the soultion i have tried

this from ibm db2 site but this also not working
SELECT VARCHAR_FORMAT(CURRENT TIMESTAMP,'DD-MON-YYYY HH24:MI

S') FROM SYSIBM.SYSDUMMY1

i have tried the other way also using substring and date then count ...bla bla..

i dont want this confused query .....since i m in field of databse administrating

can anyone tell me whats the problem inthis query

db2 => SELECT VARCHAR_FORMAT(CURRENT TIMESTAMP,'DD-MON-YYYY HH24:MI

S') FROM SYSIBM.SYSDUMMY1
SQL0171N The data type, length or value of argument "2" of routine
"SYSIBM.VARCHAR_FORMAT" is incorrect. SQLSTATE=42815

rahul_s80 · #2 (**permalink**) 08-28-09, 05:41

'DD-MON-YYYY HH24:MIS'
is not a proper format.... MM should be there

try playing with monthname/ day / year/ concat functions to get desired result

tonkuma · #3 (**permalink**) 08-28-09, 07:37

The format 'DD-MON-YYYY HH24:MI:SS' is supported on DB2 9.7 for LUW.
If you are using other platform or older DB2 version, the way of rahul_s80 would be a solution.

Here is an example:

Code:

------------------------------ Commands Entered ------------------------------
connect to SAMPLE ;
------------------------------------------------------------------------------

   Database Connection Information

 Database server        = DB2/NT 9.7.0
 SQL authorization ID   = DB2ADMIN
 Local database alias   = SAMPLE


A JDBC connection to the target has succeeded.
------------------------------ Commands Entered ------------------------------
SELECT VARCHAR_FORMAT(CURRENT TIMESTAMP,'DD-MON-YYYY HH24:MI:SS') FROM SYSIBM.SYSDUMMY1;
------------------------------------------------------------------------------

1                                       
----------------------------------------
28-AUG-2009 20:36:07                    

  1 record(s) selected.

ashrash · #4 (**permalink**) 08-28-09, 08:46

but this command is not working because i m using db2 9.1 version

it is giving error
SQL0171N The data type, length or value of argument "2" of routine
"SYSIBM.VARCHAR_FORMAT" is incorrect. SQLSTATE=42815

tonkuma · #5 (**permalink**) 08-28-09, 09:22

I already wrote:

Quote:

If you are using other platform or older DB2 version, the way of rahul_s80 would be a solution.

Cougar8000 · #6 (**permalink**) 08-28-09, 09:46

Is this what you need?

Code:

db2 "SELECT char(date(CURRENT TIMESTAMP),local) FROM SYSIBM.SYSDUMMY1"

1         
----------
08-28-2009

  1 record(s) selected.

tonkuma · #7 (**permalink**) 08-28-09, 10:06

Quote:

i want to the date from db2 in this format dd-mon-yy

Here is an example:

Code:

------------------------------ Commands Entered ------------------------------
SELECT INSERT(CHAR(CURRENT DATE, EUR), 3, 6, '-'||LEFT(MONTHNAME(CURRENT DATE), 3)||'-') FROM SYSIBM.SYSDUMMY1;
------------------------------------------------------------------------------

1                                                                                                               
----------------------------------------------------------------------------------------------------------------
28-Aug-09                                                                                                       

  1 record(s) selected.

Marcus_A · #8 (**permalink**) 08-28-09, 22:07

Here is another option:

select substr(current_date,9,2)||'-'||substr(monthname(current_date),1,3)||'-'||substr(current_date,1,4) from sysibm.sysdummy1

The DB2 scaler funcitons are described in the SQL Reference Vol 1, or search on "scaler functions" in the DB2 InfoCenter.

ashrash · #9 (**permalink**) 08-29-09, 03:54

yeah its working

i have checked using

SELECT INSERT(CHAR(CURRENT DATE, EUR), 3, 6, '-'||LEFT(MONTHNAME(CURRENT DATE), 3)||'-') FROM SYSIBM.SYSDUMMY1

but it is not working by using varchar_format and to char format

may be db2 version problem

still i m not sure whether it is version problem anyway thanks all my experienced friends

Internet Services

Web Development

Digital Marketing

Consumer Tech