Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2006
    Posts
    2

    Unanswered: Current Date Help Please...

    Hi, I have a sql statement as follows: SELECT current date - 1 day FROM sysibm.sysdummy1; This is returning the following: Aug 31, 2006 (The "- 1 day" is so I return yesterdays date - this works fine) All works great - except... I would like the format to be: 31-7-2006 I have tried everything I can - can't FORMAT the date the way I want. Any help on this would be greatly appreciated. Thanks in advance, Brad
    Last edited by coffeebreak; 08-31-06 at 00:48.

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Code:
    char(CURRENT DATE - 1 day, EUR)
    will convert that date to the representation "31.08.2006". This is the closest you can get in a simple way.
    But you can of course manipulate these 10 characters at will with SUBSTR() and CONCAT().
    Or alternatively use the functions DAY(), MONTH() and YEAR(), combined with CAST, e.g.
    Code:
    CAST(day(current date - 1 day) AS varchar(2)) || '-' ||
    CAST(month(current date - 1 day) AS varchar(2)) || '-' ||
    CAST(year(current date - 1 day) AS varchar(2))
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    Dec 2005
    Posts
    273
    you might also use the TRANSLATE-function:

    SELECT
    TRANSLATE(CHAR(CURRENT DATE - 1 DAY, EUR),'-','.')
    FROM SYSIBM.SYSDUMMY1;


    if used in a program, consider using
    SET :host = TRANSLATE(CHAR(CURRENT DATE - 1 DAY, EUR),'-','.') ;

    instead of
    SELECT
    TRANSLATE(CHAR(CURRENT DATE - 1 DAY, EUR),'-','.')
    INTO :host
    FROM SYSIBM.SYSDUMMY1 ;

  4. #4
    Join Date
    Aug 2006
    Posts
    2

    Thanks!

    Thanks for your help - works great!

Posting Permissions

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