Results 1 to 5 of 5

Thread: Date To String

  1. #1
    Join Date
    Sep 2007

    Unanswered: Date To String

    I NEED TO CONVERT A DATE type field(column) from a table which is in dd-mm-yyyy format in to a date format dd-mm-yyyy, Also need to know how addition and subtraction is done on date field.


  2. #2
    Join Date
    Jan 2003
    Provided Answers: 5
    Columns of data type DATE, TIME, and TIMESTAMP do not store the data in the format that you view the data. How you view it depends on a number of factors like your LOCALE.

    For date arithmetic see:

    DB2 Basics: Fun with Dates and Times


  3. #3
    Join Date
    Sep 2007
    I got it but say if the date is stored in string mm-dd-yyyy and want to convert it into dd-mm-yyyy form, can this be done in DB2
    for example take this Query
    select tp.PROJECT_ID id
    from expenses as tp
    where cast(tp.UPDATED_ON as char) >= cast(CURRENT DATE - 1 as char)
    and cast(tp.UPDATED_ON as char) <= cast(CURRENT DATE as char);

    How do i check this conditions, and UPDATE_ON is a TIMESTAMP datatype.
    Last edited by donraja_ht; 04-23-09 at 01:25.

  4. #4
    Join Date
    Dec 2005
    DATE is stored in an internal format. There is no need to convert it for your query. Simply code:

    select tp.PROJECT_ID id
    from expenses as tp
    where tp.UPDATED_ON >= CURRENT DATE - 1 DAY
    and tp.UPDATED_ON as char <= CURRENT DATE;

  5. #5
    Join Date
    Jan 2007
    Jena, Germany
    If you have a string that you interpret as a date, i.e. it is a value of type VARCHAR or CHAR but not a DATE, I suggest that you apply the DATE() function to convert the string to a date value and use that. You can do the conversion in a view if you won't/can't change the schema definition.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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