'D' || A.ZARF_ECARS_1 || A.legacy_branch_rented as Transmittal_Number,
A.ADDRESS1 as Address,
A.CITY as CITY1,
A.STATE as STATE1,
A.POSTAL as ZIP,
A.ITEM as Debtor_Reference,
cast(A.due_DT as date format 'MM/DD/YYYY') as DueDt,
A.BAL_AMT as Amount_Due,
cast(a.zarf_ecars_1 as decimal(6,0)) as LTicket,
cast(max(b.accounting_dt) as date format 'MM/DD/YYYY') as PSLastPay
FROM PSFS.BWS_CUST_AR_MAST A
left outer join psfs.BWS_CUST_AR_MAST_ACTIVITY b on a.item=b.item and b.ENTRY_TYPE='PY'
and cast(A.asof_DT as date format 'MM/DD/YYYY') between
cast( '01/01/2016 00:00:00' as date format 'MM/DD/YYYY') and
cast( '01/31/2016 00:00:00' as date format 'MM/DD/YYYY')
When I try to run this sql, I get an error message that says "Select Failed.  Invalid date supplied for BWS_CUST_AR_MAST asof_dt. This column is defined as TIMESTAMP (0). Any help would be greatly appreciated. I'm not sure what is more confusing dates or the CAST feature. thanks for your help
Hope this helps.
cast(A.asof_DT as date) between
cast(cast( '2016-01-01 00:00:00' as timestamp(0)) as date) and
cast(cast( '2016-01-31 00:00:00' as timestamp(0)) as date)
There's no need for CASTs, better use DATE/TIMESTAMP literals:
and cast(A.asof_DT as date)
between DATE '2016-01-01' AND DATE '2016-01-31'
or simplified to:
between TIMESTAMP '2016-01-01 00:00:00' AND TIMESTAMP '2016-01-31 23:59:59'