Results 1 to 2 of 2

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

  1. #1
    Contributor
    Join Date
    Sep 2012
    Location
    Chicago, IL USA
    Posts
    5

    Default Basic Node - Filtering & MM/DD/YYYY

    Hello,

    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?

    Thanks

    node:West_EDW_OCC
    bretype:basic::querydump
    editor:Label=West EDW OCC
    editor:sortkey=4b758947121608f8_9
    output:@40fd2c7444283da6/=
    prop:DBInterface=cli
    prop:DBService=cnewp.sbc.com
    prop:DBUser=xxxxxx
    prop:QueryFile=<<EOX
    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
    Location
    Dallas, TX
    Posts
    297

    Default

    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
  •