Database Query Date Handling

Question

Background

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.

Problem

The problem occurred whilst Executing my SQL query and looking in the log file I had an Oracle error (ORA-01843).

Resolution

Looking on the web I found an article regarding this problem which led me onto another page. This page told me to use the oracle SQL syntax TO_DATE.

Using this syntax around my dates being searched stopped the ODBC driver from stopping the execution of my script.

unknown · Answer

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:

pmroz · Answer

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);

sql =

"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?

txnelson · Answer

Could you please provide the script as an attachment?  Debugging your description is pretty much a guessing game without the specifics.