Understanding SQL Date Formatting for QuickReports, IALs, and SQL Query Tool

We’re getting run-time errors due to date logic/formatting/selections. Is there any standardized set of date formatting rules?

I have numerous Oracle SQL websites detailing date functions, but we run into issues every now and then, with each case having slightly different requirements/logic.

Scenarios:

1.) SQL in an IAL runs fine in the quick report using it, but fails when run in the SQL Query Tool.

2.) A quick report runs fine (and also runs fine with RMB Output>Save As...) but if you take Export to Excel it fails.

For instance, our latest situation is this error when running an IAL’s logic from the SQL Query Tool that doesn’t occur when running a quick report over the same IAL:

IAL logic:

select
soi.sub_program_id as PGM,
cois.district_code as Class,
omi.commission_receiver as Receiver,
max(cin2.name) as Receiver_Name,
max(cia.name) as Receiver_Company,
omi.customer_id as Dealer,
max(cin.name) as Dealer_Name,
max(cois.country_code) || ' - ' || max(cia.state) as CN_ST,

sum(CASE WHEN trunc(cois.invoice_date) between to_date( '#START_OF_LAST_MONTH#', 'YYYY-MM-DD-HH24:MI:SS' ) and to_date( '#END_OF_LAST_MONTH#', 'YYYY-MM-DD-HH24:MI:SS' )
then cois.gross_amount else 0 end) as Month_Sales,

sum(CASE WHEN TRUNC(to_char(cois.invoice_date, 'YYYYMM')) = to_char( ADD_MONTHS( to_date('#END_OF_LAST_MONTH#','YYYY-MM-DD'), -12), 'YYYYMM' ) 
then cois.gross_amount else 0 end) as  Prior_Year_Month_Sales,

sum(CASE WHEN TRUNC(to_char(cois.invoice_date, 'YYYY')) = to_char( to_date('#END_OF_LAST_MONTH#','YYYY-MM-DD'), 'YYYY' ) 
then cois.gross_amount else 0 end) as  Current_YTD_Sales,

sum(CASE WHEN TRUNC(to_char(cois.invoice_date, 'YYYY')) = to_char( ADD_MONTHS( to_date('#END_OF_LAST_MONTH#','YYYY-MM-DD'), -12), 'YYYY' ) 
then cois.gross_amount else 0 end) as  Prior_YTD_Sales,

min(cicm.value) as Phone_Number

from REGA1APP.c_override_master_line omi

left join rega1app.C_SUPPLIER_OVERRIDE_INFO soi   on omi.supplier_id = soi.supplier_id and omi.sub_program_id = soi.sub_program_id

left join rega1app.CUST_ORD_INVO_STAT  cois on  omi.customer_id = cois.customer_no

left join rega1app.Sales_Part sp on cois.catalog_no = sp.catalog_no and cois.contract = sp.contract

left join rega1app.customer_info_address cia on soi.supplier_id = cia.customer_id and omi.supplier_id = cia.address_id

left join rega1app.customer_info cin2 on omi.supplier_id = cin2.customer_id

left join rega1app.customer_info cin on omi.customer_id = cin.customer_id

left join rega1app.customer_info_comm_method cicm  on omi.customer_id = cicm.customer_id and cicm.method_id_db = 'PHONE'  and cicm.method_default = 'TRUE'

where cois.gross_amount <> 0
and soi.sub_program_id in ('AVP', 'AVP2', 'AM', 'DIV')
and omi.commission_receiver = '114043' and omi.customer_id = '114043'

group by soi.sub_program_id, cois.district_code, omi.commission_receiver, omi.customer_id
order by soi.sub_program_id, cois.district_code, omi.commission_receiver, omi.customer_id