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)
5 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

Junior Contributor

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'

Re: Select Failed 2666 Invalid date supplied

Format:HTML Format Version:1.0 StartHTML: 165 EndHTML: 1592 StartFragment: 314 EndFragment: 1560 StartSelection: 314 EndSelection: 314SyntaxEditor Code Snippet

 SEL cast( LockedDate as date)
from DBC.dbase
  WHERE   databasename ='xyz';

Above query failing with Invalid date supplied for DBC.dbase.

 I need to convert Lockeddate - Integer Format to Date format.

Junior Contributor

Re: Select Failed 2666 Invalid date supplied

Check the source code of dbc.UsersV, there's how to get a Date from the integer LockDate (same for PasswordChgDate):

 ((100 * ((4 * DBASE.LockedDate - 1) / 146097)
   + (4 * (((4 * DBASE.LockedDate - 1) MOD 146097) / 4)
   + 3) / 1461 - 1900) + ((5 * (((4 * (((4 * DBASE.LockedDate
   - 1) MOD 146097) / 4) + 3) MOD 1461 + 4) / 4) - 3) / 153 + 2)
   / 12) * 10000 + (((5 * (((4 * (((4 * DBASE.LockedDate - 1)
   MOD 146097) / 4) + 3) MOD 1461 + 4) / 4) - 3) / 153 + 2) MOD 12
   + 1) * 100 + ((5 * (((4 * (((4 * DBASE.LockedDate - 1) MOD
   146097) / 4) + 3) MOD 1461 + 4) / 4) - 3) MOD 153 +5) / 5
   (date, format 'yy/mm/dd', Named LockedDate),

But this returns exactly the same as a much simpler:

DATE '0001-01-01' + (LockDate-307)

 

Teradata Employee

Re: Select Failed 2666 Invalid date supplied

One should always use DBC views rather than direct access to DBC tables. DBC.UsersV rather than DBC.DBASE in this case.