Hi all,
I am having some difficulty with a SQL query. My goal is to use user defined dates from the calendar box() function to modify each sql query to pull data from between those date ranges. This is the code for the calenderbox:
Cancel = "0";
New Window( "ZFG Cycle Start Date",
<<Modal,
cal1 = Calendar Box(),
cal1 << Show Time(0);
Button Box( "OK", cal2 = cal1 << get date;),
Button Box( "Cancel", Cancel = "1") );
cal3 = MDYHMS(cal2);
If(Cancel =="1", Stop());
New Window( "ZFG Cycle End Date",
<<Modal,
cal4 = Calendar Box(),
cal4 << show time(0);
Button Box( "OK", cal5 = cal4 <<get date;),
Button Box( "Cancel", Cancel = "1") );
If(Cancel =="1", Stop());
cal6 = mdyhms(cal5);
And this is my code for the SQL query. I have highlighted where the code breaks down:
sql_script = evalinsert(
"SELECT *
FROM OHT_MOVES_FLATFILE
Where day <= To_Date('^cal6^', 'M/D/Y')
and day >= To_Date('^cal3^', 'MDY')
and vehicle_type = 'FOUP'
and dst_tool= ANY('ZFG1_17C01','ZFG1_17C02', 'ZFG1_17W01', 'ZFG_17W02')
and rownum <100000
Order by request_time");It runs fine when I enter a date manually in this format:
day <= To_Date('07/17/2017', 'MM/DD/YYYY')
Using the variable from the calenderbox(), returns one of the following errors depending on how I have tried to set it up. The calendar box variable will return the date in this format: "07/17/2017 2:07:14 PM"
[Oracle][ODBC][Ora]ORA-01843: not a valid month
[Oracle][ODBC][Ora]ORA-01830: date format picture ends before converting entire input string
Any recommendations you folks have would be appreciated
You'll have to chop off the timestamp from your date in order for Oracle to process it properly.
cal3 = substr(MDYHMS(cal2), 1, 10);
cal6 = substr(mdyhms(cal5), 1, 10);
Alternatively if you want to be more granular you can provide the appropriate date/time format to include the time.
