Results 1 to 6 of 6

Thread: New BRE User - Date Calculation

  1. #1

    Default New BRE User - Date Calculation

    How do I add a new field to an output and what node should be used. I want to calculate how many days since a customer ordered service based upon order_date field in the existing output table. In SQL I what write it as ((current_date - order_date) Days active.

  2. #2
    Contributor
    Join Date
    Feb 2009
    Location
    Chicago
    Posts
    194

    Default

    hi mw9286, assuming you want this per record you can add a filter node and use the date subtract function to get the difference. I'll see if i have an example somewhere.

    Wayne

  3. #3

    Default

    Hi Wayne:

    Thanks for the information.

    I see how the dateSubtract function works if I am comparing two date fields that exist in an output report.

    Now I need to find if there is a term like current_date or SYSdate that can be used in the function. (dateSubtract(CURRENT_DATE,ORDER_DATE)

  4. #4
    Lavastorm Employee stonysmith's Avatar
    Join Date
    Nov 2006
    Location
    Grapevine Tx
    Posts
    799

    Default

    All you need is date()

    dateSubtract(date(),ORDER_DATE)

  5. #5
    New Contributor
    Join Date
    Oct 2016
    Location
    Atlanta, Georgia
    Posts
    1

    Default

    I'm using dateSubtract where there are some null values. I'm trying to handle the null values by using dateSubtract(date(1999,12,31),FieldName) and I'm getting different results for multiple rows.

  6. #6
    Lavastorm Employee
    Join Date
    Nov 2012
    Location
    Warrington, UK
    Posts
    244

    Default

    Hi

    If I use dateSubstract() on some sample data I always get the same result if the input field contains Null - i.e. the output is also NULL

    Code:
    node:Filter
    bretype:core::Filter
    editor:sortkey=5b0fdf2525ab0d96
    input:@40fd2c74167f1ca2/=Sample_Data.40fe6c55598828e5
    output:@40fd2c7420761db6/=
    prop:Script=<<EOX
    # dateSubtract(date(1999,12,31),FieldName)
    
    diff = dateSubtract(date(1999,12,31),dueDate)
    
    
    emit *, diff
    
    EOX
    editor:XY=450,160
    end:Filter
    
    node:Sample_Data
    bretype:core::Static Data
    editor:Label=Sample Data
    editor:sortkey=5b0fde7e54a61cf0
    output:@40fe6c55598828e5/=
    prop:StaticData=<<EOX
    id:int,dueDate:date
    1,2018-01-01
    2,NULL
    3,2018-02-02
    4,NULL
    5,2018-04-02
    EOX
    editor:XY=330,160
    end:Sample_Data
    Would you be able to post a small sample of the input data that will exhibit this issue you are seeing?

    Thanks,
    Adrian

Posting Permissions

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