Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2004
    Posts
    35

    Unanswered: help need with converting oracle query to db2 using date manipulation

    hi ,
    i have a query that manipulate date column in oracle
    that needs to be converted to db2.

    the oracle query:
    select TRUNC(emeh_date_time - -7/24) + -7/24
    from <table>

    whats the db2 version??

    10x

  2. #2
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by ronenshi
    hi ,
    i have a query that manipulate date column in oracle
    that needs to be converted to db2.

    the oracle query:
    select TRUNC(emeh_date_time - -7/24) + -7/24
    from <table>

    whats the db2 version??

    10x
    What does it do in Oracle?
    --
    Jonathan Petruk
    DB2 Database Consultant

  3. #3
    Join Date
    Sep 2004
    Posts
    35
    the trunc function in oracle truncate the time from the datetime .
    and in the statement context: its truncating the emeh_date_time and adds hours then remove hours

  4. #4
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by ronenshi
    the trunc function in oracle truncate the time from the datetime .
    and in the statement context: its truncating the emeh_date_time and adds hours then remove hours
    A little confused as to why you'd want to add then remove hours? But anyway you're probably looking for the TIME function combined with the adding of a duration, ie:

    TIME(CURRENT TIMESTAMP) + 3 hours

    jono
    --
    Jonathan Petruk
    DB2 Database Consultant

  5. #5
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Ahh, I think you mean that the time is truncated, not return, I think I misread.

    DATE(blah) will do the trick on that one then. You can still use the duration.
    --
    Jonathan Petruk
    DB2 Database Consultant

  6. #6
    Join Date
    Sep 2004
    Posts
    35
    yes i need to had time to a date function
    like select date(<col type timestamp>) + 3 hours (3 is a parameter)

    can u specify the full select cause i tried it and it didnt work
    10x

  7. #7
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Quote Originally Posted by ronenshi
    yes i need to had time to a date function
    like select date(<col type timestamp>) + 3 hours (3 is a parameter)

    can u specify the full select cause i tried it and it didnt work
    10x
    I'm still not 100% clear on what you're trying to do.

    DATE(timestamp) will return the date portion.

    You can't add hours to a DATE though, only days, months, etc. But you can add hours to the timestamp before you apply it.

    So
    DATE(CURRENT TIMESTAMP + 3 HOURS)
    or
    DATE(CURRENT TIMESTAMP) + 1 DAY

    should both work... replacing CURRENT TIMESTAMP with what I presume is your TIMESTAMP column.
    --
    Jonathan Petruk
    DB2 Database Consultant

  8. #8
    Join Date
    Sep 2004
    Posts
    35
    10x thats what i was looking 4 :-)

Posting Permissions

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