Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2011
    Location
    Chennai
    Posts
    27

    Unanswered: Formatting the date

    Hi All,

    I'm working on a script needs a old date with a specified format.

    currently i'm able to get the date with different format

    current format
    db2 -x "select current date + 1 month - day(current date + 1 month) days - 2 YEARS - 1 MONTHS from SYSIBM.SYSDUMMY1"

    07/31/2010

    required format
    YYYY-MM-DD

    can someone help me out to get the req format without altdate!!

    SYSTEM AIX
    db2level v97

    Thanks
    Mohamed Thawfeek I

  2. #2
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    Several different ways to do it.
    If you want to have a pre-defined ISO date format (instead of DB2 working out the date format from the locale of the client), then you can use the varchar_format() function , or other functions.

    Example:
    values varchar_format(current date, 'YYYY-MM-DD')

  3. #3
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Several different ways to do it.
    If you want to have a pre-defined ISO date format ...
    Example:
    Code:
    ------------------------------ Commands Entered ------------------------------
    VALUES CHAR(current date , ISO);
    ------------------------------------------------------------------------------
    
    1         
    ----------
    2012-08-26
    
      1 record(s) selected.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    select current date + 1 month - day(current date + 1 month) days - 2 YEARS - 1 MONTHS from SYSIBM.SYSDUMMY1
    I wonder if this was what you really want?

    Because, if replaced "current date" with "DATE('2013-02-15')", it returned
    Code:
    ------------------------------ Commands Entered ------------------------------
    select DATE('2013-02-15') + 1 month - day(DATE('2013-02-15') + 1 month) days - 2 YEARS  - 1 MONTHS from SYSIBM.SYSDUMMY1;
    ------------------------------------------------------------------------------
    
    1         
    ----------
    2011-01-28
    
      1 record(s) selected.
    What was rational meaning of "January 28"?

  5. #5
    Join Date
    Nov 2011
    Location
    Chennai
    Posts
    27
    thanks guys ,
    i'm able to get the format i need by using varchar_format.

    db2 -x "values varchar_format(current date + 1 month - day(current date + 1 month) days - 2 YEARS - 1 MONTHS, 'YYYY-MM-DD')"
    2010-07-31

    and db2 "select DATE('2013-02-15') + 1 month - day(DATE('2013-02-15') + 1 month) days - 2 YEARS - 1 MONTHS from SYSIBM.SYSDUMMY1"

    @tonkuma : I still wonder why its giving that jan 28 output .. it have to be 31st of jan.

    thanks
    Mohamed Thawfeek I

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I still wonder why its giving that jan 28 output ...
    Please see intermediate results step by step ...
    Code:
    ------------------------------ Commands Entered ------------------------------
    select DATE('2013-02-15') + 1 month - day(DATE('2013-02-15') + 1 month) days
     FROM  sysibm.sysdummy1;
    ------------------------------------------------------------------------------
    
    1         
    ----------
    2013-02-28
    
      1 record(s) selected.
    
    
    ------------------------------ Commands Entered ------------------------------
    select DATE('2013-02-15') + 1 month - day(DATE('2013-02-15') + 1 month) days
     - 2 YEARS
     FROM  sysibm.sysdummy1;
    ------------------------------------------------------------------------------
    
    1         
    ----------
    2011-02-28
    
      1 record(s) selected.
    
    
    ------------------------------ Commands Entered ------------------------------
    select DATE('2013-02-15') + 1 month - day(DATE('2013-02-15') + 1 month) days
     - 2 YEARS
     - 1 MONTHS
     FROM  sysibm.sysdummy1;
    ------------------------------------------------------------------------------
    
    1         
    ----------
    2011-01-28
    
      1 record(s) selected.

    If you want 31st of jan for DATE('2013-02-15'),
    try ...
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT DATE('2013-02-15') - DAY(DATE('2013-02-15')) DAYS - 2 YEARS
     FROM  sysibm.sysdummy1;
    ------------------------------------------------------------------------------
    
    1         
    ----------
    2011-01-31
    
      1 record(s) selected.
    or

    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT current date - DAY(current date) DAYS - 2 YEARS
     FROM  sysibm.sysdummy1;
    ------------------------------------------------------------------------------
    
    1         
    ----------
    2010-07-31
    
      1 record(s) selected.
    Last edited by tonkuma; 08-25-12 at 15:36.

  7. #7
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    Another way to do this is with the LAST_DAY function:
    Code:
    SELECT CHAR(LAST_DAY(CURRENT DATE - 2 YEARS - 1 MONTH), ISO) FROM SYSIBM.SYSDUMMY1
    
    1         
    ----------
    2010-07-31

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    While LAST_DAY was supporeted from DB2 9.7 for LUW,
    it may be better than my longer expression.

    If considered leap years, my expression should be modified, like ...
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT DATE('2014-03-15') - 2 YEARS - DAY(DATE('2014-03-15') - 2 YEARS) DAYS
     FROM  sysibm.sysdummy1;
    ------------------------------------------------------------------------------
    
    1         
    ----------
    2012-02-29
    
      1 record(s) selected.
    or
    Code:
    ------------------------------ Commands Entered ------------------------------
    SELECT CURRENT DATE - 2 YEARS - DAY(CURRENT DATE - 2 YEARS) DAYS
     FROM  sysibm.sysdummy1;
    ------------------------------------------------------------------------------
    
    1         
    ----------
    2010-07-31
    
      1 record(s) selected.

Posting Permissions

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