Results 1 to 4 of 4

Thread: Date/time

  1. #1
    Join Date
    Nov 2006
    Posts
    31

    Unanswered: Date/time

    Hi all,
    I have two columns OPEN_DATE (ex...2007-03-27) and OPEN_TIME (ex...15:20:54)
    The time format I am required to display - mm/dd/yyyy hh:mm
    I am using the below script

    select ltrim(rtrim(char(month(open_date))))|| '/' ||ltrim(rtrim(char(day(open_date))))||'/'||ltrim(rtrim(char(year(open_date))))||' '||ltrim(rtrim(char(hour(open_time))))||':'||ltrim (rtrim(char(minute(open_time)))) from <table>

    Result : 3/27/2007 15:20

    The problem is how do I get the month as '03' instead of just '3'

    Thanks in advance..

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Instead of using month, day, year, etc functions, I would use the substr funciton of the date column for each of these.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jul 2006
    Location
    Pune , India
    Posts
    433
    db2 values char(date ('2007-03-27'),usa)
    1
    ----------
    03/27/2007

    and then concat the time
    Rahul Singh
    Certified DB2 9 DBA / Application Developer

  4. #4
    Join Date
    Nov 2006
    Posts
    31

    Thanks..

    Great.....Thanks!!!

Posting Permissions

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