All Forums Database
shaves 22 posts Joined 04/15
03 Feb 2016
Select Failed 2666 Invalid date supplied

SELECT
'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'

where a.business_unit='D9001'
and a.cust_field_c2='S'
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')
and a.bal_amt>0

group by
Transmittal_Number,
Address,
CITY1,
STATE1,
ZIP,
Debtor_Reference,
DueDt,
Amount_Due,
LTicket
When I try to run this sql, I get an error message that says "Select Failed.  [2666] 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
 

dins2k2 51 posts Joined 05/13
03 Feb 2016

Hi Shaves,
 
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)

 
Thanks,
Dinesh

dnoeth 4628 posts Joined 11/04
03 Feb 2016

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:

and A.asof_DT
between TIMESTAMP '2016-01-01 00:00:00' AND TIMESTAMP '2016-01-31 23:59:59'

 

Dieter

You must sign in to leave a comment.