Select Failed 2666 Invalid date supplied

Database
Enthusiast

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

Tags (3)
2 REPLIES
Enthusiast

Re: Select Failed 2666 Invalid date supplied

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

Senior Apprentice

Re: Select Failed 2666 Invalid date supplied

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'