'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'
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)
WHERE databasename ='xyz';
Above query failing with Invalid date supplied for DBC.dbase.
I need to convert Lockeddate - Integer Format to Date format.
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)
One should always use DBC views rather than direct access to DBC tables. DBC.UsersV rather than DBC.DBASE in this case.