Results 1 to 2 of 2

Thread: Basic Node - Filtering & MM/DD/YYYY

  1. #1
    Join Date
    Sep 2012
    Chicago, IL USA

    Default Basic Node - Filtering & MM/DD/YYYY


    The node below queiries data with a data column which has a format of MM/DD/YYYY. Lavastorm seems to populate the data as MM/DD/YYYY 00:00:0000 with the output of the node. We believe we get errors from the WHERE statements in the node becuase the where statements are built to filter for MM/DD/YYYY. Is there a way to ensure that the data from the date column is aquired as MM/DD/YYYY during aquisition? The second question is there a way to write the filtering to accomodate both formats?

    Or should we have the user convert those basic nodes to core:DB Query nodes?


    editor:Label=West EDW OCC
    select *
    from access.v83b10
    Where cabs_bill_date between '01/10/2015' (date, format 'mm/dd/yy')
    and '11/30/2015' (date, format 'mm/dd/yy')
    and ban = '0730960566777'
    and service_order_number = 'C78883305'
    --and phrase_code = '759'
    --and service_order_comp_date >= '20110726' (date, format 'yyyymmdd')
    --and from_date >= '20110421' (date, format 'yyyymmdd')
    --and phrase_code in ('M63','M64','032','033')

  2. #2
    Lavastorm Employee
    Join Date
    Dec 2006
    Dallas, TX


    Couple questions:
    What is the database type you are using?
    What is the error message you are getting?
    Have you been able to run this query outside of LAE? (because I'm wondering if this is a problem getting the SQL select statement syntax right and not an LAE related issue)

    To try to answer the question, "Is there a way to ensure that the date from the date column is acquired as MM/DD/YYYY during acquisition?" The answer is yes, and the way to do it depends on the syntax for the database you are using. All you do is cast the date column value to a string. But I don't know why you would want to do this. If you leave it with "select *" any date data types in the database will be acquired as timestamp types in LAE and those do not carry a specific format. It is much easier to work with timestamps than work with dates in some string. Then, if you need to, you can always convert to a string in any format you like when creating a report.

Posting Permissions

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