Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2016
    Posts
    2

    Exclamation Unanswered: Need help getting two date/time formulas to subtract

    here is the code i have:

    to_number(to_char((case when({systemnotes.oldvalue}='Design In Progress') and ({systemnotes.newvalue}='Manufacturing (Approved)') then {systemnotes.date} end, 'MI'))) - to_number(to_char((case when({systemnotes.oldvalue}='DH PENDING') and ({systemnotes.newvalue}='Design In Progress') then {systemnotes.date} end, 'MI')))

    trying to subtract the times i get with these fields to get the time it took to go from one value to the next in minutes.

  2. #2
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,505
    1) you must subtract a date from a date, never convert to a number. The subtraction returns a number. 1 = 1 day, .5 would be 12 hours...etc

    2) The use of braces "{}" is not allowed in oracle. What language are you porting from?

    Give a little background on what you are trying to do.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  3. #3
    Join Date
    Apr 2016
    Posts
    2
    Quote Originally Posted by beilstwh View Post
    1) you must subtract a date from a date, never convert to a number. The subtraction returns a number. 1 = 1 day, .5 would be 12 hours...etc

    2) The use of braces "{}" is not allowed in oracle. What language are you porting from?

    Give a little background on what you are trying to do.
    okay, well im using a database on netsuite that makes me use {} for the fields, this one almost works but tu

    to_number(to_char(case when({systemnotes.oldvalue}='DH PENDING') and ({systemnotes.newvalue}='Design In Progress') then {systemnotes.date} end, 'HHMI')) - to_number(to_char(case when ({systemnotes.oldvalue}='Design In Progress') and ({systemnotes.newvalue}='Manufacturing (Approved)') then {systemnotes.date} end, 'HHMI'))

Posting Permissions

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