SQLTeam.com | Weblogs | Forums

Sudden error message I don't understand

I had a script that was working fine:
Select events2do_view.site_responsible_description, all_clients_view.id_no, staff_view.end_date, SU.staff_name as SN, staff_view.agency_name, all_clients_view.full_name, events2do_view.duration_scheduled,

events2do_view.check_in_complete, events2do_view.is_completed, events2do_view.event_log_actual_date,

events2do_view.days_overdue_staff, events2do_view.check_out, events2do_view.last_name, events2do_view.staff_name,

events2do_view.first_name, people.phone_day, people.mobile_phone, people.Contact_by_phone, people.contact_by_sms, people.contact_by_email, people.email_address, events2do_view.event_name, sms_phone_providers.description, events2do_view.scheduled_date, events2do_view.is_manual, events2do_view.write_off_reason_desc, events2do_view.scheduled_date AS Date2, events2do_view.scheduled_date_tzo, event_log_is_noshow, is_write_off, staff_view.first_name AS staff_first

From events2do_view

JOIN people ON people.people_id = events2do_view.people_id

JOIN all_clients_view on events2do_view.people_id = all_clients_view.people_id

LEFT JOIN sms_phone_providers on sms_phone_providers.sms_phone_providers_id = people.sms_phone_providers_id

JOIN staff_view ON staff_view.staff_id = events2do_view.staff_responsible

JOIN staff on staff_view.staff_id = staff.staff_id

JOIN staff_view as SU on staff.supervisor_id = SU.staff_id
where events2do_view.scheduled_date > 07062019

but now gives this error: Do you know what this error means and how i can try to debug this?

Logi Debugger Trace Report

There was an error while processing your request.

The error was:

There was a problem running a DataLayer. The error was: Arithmetic overflow error converting expression to data type datetime.

the issue is something has probably changed in the underlying data.

what do you get when you do the following

select * From events2do_view where ISDATE(scheduled_date) = 0

yes this is working fine this script you sent.

that means one of your data is not good. ISDATA = 0 means one of those rows is not a valid date and you are doing

where events2do_view.scheduled_date > 07062019

what data type is scheduled_date int?

schedule_date
Data Type Character Length Numeric Precision Numeric Scale
DBTimeStamp 8 23 3

If this is SQL Server, try this:

where events2do_view.scheduled_date > '20190706' --or '20160607' if it's a June date rather than a July date