Skip to Content
Dec 03, 2021 at 11:02 PM

FMS converting Time and date not working


Hi experts, im new into this, i have a question, we have a FMS that converts 2 UDF into 'Production Time' and SUM two fields in work orders.

It works pretty fine for almost all hours, except at certain hours, like this example:

Start Time - End Time Production Time
12:50 A.M - 05:40 A.M

1). [Microsoft][SQL Server Native Client 11.0][SQL Server]The conversion of a varchar data type to a datetime data type resulted in an out-of-range value. FMS execution failed on field 'U_p_time' with query name '07 Production Time'

This is the query for Formatted Search
declare @Date_start datetime
declare @Time_start_1 float
declare @Time_start time

declare @Date_end datetime
declare @Time_end_1 float
declare @Time_end time

set @Date_start = $[IGE1.u_startdate]
set @Time_start_1= $[IGE1.u_start_time]

set @Time_start= CONVERT(varchar(15),CAST(CONVERT(datetime,LEFT(@Time_start_1,2)+':'+right(@Time_start_1,2)) AS TIME),100)

set @Date_end = $[IGE1.u_end_date]
set @Time_end_1 = $[IGE1.u_end_time]
set @Time_end= CONVERT(varchar(15),CAST(CONVERT(datetime,LEFT(@Time_end_1,2)+':'+right(@Time_end_1,2)) AS TIME),100)
set @Date_start = @Date_start + cast(@Time_start as datetime)
set @Date_end = @Date_end + cast(@Time_end as datetime)
select Cast((@Date_end - @Date_start) as Float) * 24.0
from wor1 t0
where t0.DocEntry=$[IGE1.baseref.0] and t0.linenum=($[$13.60.number] -1)

Could you please take a look and see what its wrong?
A colleague of mine told me to look into SQL server but i never ran FMS in there.

Thanks so much for the help