Database Query Date Handling
Whilst working on part of an application I needed JMP to fill the gaps of my dataset using know variables from the imported dataset with the information already contained in my Database. These known variables used specific dates to search the database to find the required information.
There was a requirement for this script to be repeatable. Therefore I did not want to change the format of any of the original columns. I did have a data quality plan with the customer that would ensure the data being used would be in the same format.
The script would be required to pick up the specific date and update variables in a SQL script.
The problem occurred whilst Executing my SQL query and looking in the log file I had an Oracle error (ORA-01843).
Using this syntax around my dates being searched stopped the ODBC driver from stopping the execution of my script.
Could you please provide the script as an attachment? Debugging your description is pretty much a guessing game without the specifics.
The error message ORA-01843 means that you do not have a valid month.
I routinely use TO_DATE in SQL queries that I've built in JSL and executed with ODBC. For example here's a table with a column of type DATE called MY_DATE:
dbc = create database connection(dsn_string);
"SELECT * FROM myschema.mytable m
WHERE m.my_date = to_date('13-Jan-2015','DD-Mon-YYYY')";
dt = execute sql(dbc, sql);
close database connection(dbc);
Can you provide a snippet of your script that handles the dates?
orry guys - I should have mentioned that this was a description to a problem I faced that has been solved. I felt it relevant to share my experience with this date issue for other users to see.
The code in all respects now looks like the following snippet as provided by @PMRoz: