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
