Hitachi Vantara Pentaho Community Forums
Results 1 to 3 of 3

Thread: JS - Date Query returning NaN

  1. #1
    Join Date
    Apr 2009
    Posts
    7

    Default JS - Date Query returning NaN

    What am I doing wrong with my formatting below?

    I want to run the following SQL with red text as a variable input string:
    SELECT
    TO_CHAR(ADD_MONTHS(TO_DATE('11-10-2009','mm-dd-yyyy'),-2),'MONTH') Month
    from dual

    The purple text is a string I generate from JavaScript, the red text comes from an input variable.
    date_offset = "TO_DATE('" + "11-10-2009" + "','mm-dd-yyyy')"; <= Good!


    date_offset = "TO_DATE('" + {DATE_INPUT} + "','mm-dd-yyyy')"; <= Bad.. (NAN)!

    I have verified that {DATE_INPUT} contains a valid date (by dumping it straight to the query above and checking the SQL generated in the exception dump which is pasted below with red text being the variable:

    "TO_DATE('" + "12-11-2009" + "','mm-dd-yyyy')" 12-09-2009

  2. #2
    Join Date
    Apr 2009
    Posts
    7

    Default

    The solution was to remove the quotes from around my input variable.

    works:
    audit_date_offset = "TO_DATE('{audit_date}', 'mm-dd-yyyy')" ;

    fails:
    audit_date_offset = "TO_DATE('" + {audit_date}+ "', 'mm-dd-yyyy')" ;

  3. #3
    Join Date
    Apr 2008
    Posts
    4,690

    Default

    Question:

    Why cast back and forth?

    The following should work (but then again, I'm not an Oracle person, so it might not...)
    Code:
    SELECT
    TO_CHAR(ADD_MONTHS({audit_date},-2),'MONTH') Month
    from dual

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.