Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2009
    Posts
    19

    Red face Unanswered: db2 date format problem

    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:MIS') 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:MIS') FROM SYSIBM.SYSDUMMY1
    SQL0171N The data type, length or value of argument "2" of routine
    "SYSIBM.VARCHAR_FORMAT" is incorrect. SQLSTATE=42815

  2. #2
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    '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
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  4. #4
    Join Date
    Aug 2009
    Posts
    19

    thank you tonkuma

    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

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I already wrote:
    If you are using other platform or older DB2 version, the way of rahul_s80 would be a solution.

  6. #6
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    Is this what you need?

    Code:
    db2 "SELECT char(date(CURRENT TIMESTAMP),local) FROM SYSIBM.SYSDUMMY1"
    
    1         
    ----------
    08-28-2009
    
      1 record(s) selected.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Aug 2009
    Posts
    19

    thanks everyone

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •