Subscribe Bookmark RSS Feed

Calendar Box Sql query

MRB

New Contributor

Joined:

Jul 20, 2017

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

1 ACCEPTED SOLUTION

Accepted Solutions
pmroz

Super User

Joined:

Jun 23, 2011

Solution

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.

2 REPLIES
pmroz

Super User

Joined:

Jun 23, 2011

Solution

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.

MRB

New Contributor

Joined:

Jul 20, 2017

Ah perfect, works great thanks!!