Results 1 to 8 of 8
  1. #1
    Join Date
    Oct 2012
    Posts
    4

    Red face Unanswered: TO change date format in DB2

    Hi Everyone ,

    Need help !!!

    "I want to change the date format in DB2"
    I am doing
    db2 "select max(MONTH_LAST_DATE) from table_name"
    Out is "09/30/2012"
    I want to convert into "30-SEP-2012" format.

    Also, I want to subtract " ( 30-SEP-2012 ) - 6 month "

    Thanks in advance...

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What you see when you select a date column is dependent on the client software. It has nothing to do with how DB2 stores the data. If you want to see it in a non-normal format, then you need to write a UDF to convert it to the format you want.

    Andy

  3. #3
    Join Date
    Oct 2012
    Posts
    4
    Hi Andy,
    thanks for reply .
    I am new to DB2.
    Would you please elabroate it.
    I already share the output

  4. #4
    Join Date
    Apr 2008
    Posts
    39
    You can change date formate something like mentioned below.
    You need to change the sql as per your requirment.

    db2 "select char (MONTH_LAST_DATE,iso) from table_name"
    db2 "select char (MONTH_LAST_DATE,eur) from table_name"
    db2 "select char (MONTH_LAST_DATE,jis) from table_name"

    Thanks
    Naval K

  5. #5
    Join Date
    Oct 2012
    Posts
    4
    thanks .. its working ..
    db2 -x select max (char (MONTH_LAST_DATE,iso) ) from table_name
    output is 2012-09-30.

    Now I want to ( 2012-09-30) - 6 month , Please suggest the syntax .

  6. #6
    Join Date
    Apr 2008
    Posts
    39
    use something like mentioned below.

    db2 -x select max (char (MONTH_LAST_DATE- 6 Months,iso) ) from table_name

    Thanks
    Naval K

  7. #7
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    all details on dates :
    Datetime operations and durations
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  8. #8
    Join Date
    Oct 2012
    Posts
    4
    Thanks Naval K, its working fine .
    thanks for support

Posting Permissions

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