sql - Msg 242: conversion of a varchar data type to a datetime data type resulted in an out-of-range value -

i have gone through bunch of posts here error , tried changing data types , used convert nothing seems fix this. ask here. try give info, feel free ask if not enough.

this getting error:

insert prompt(id, date)       select          row_number() on (order b.idloc),         [dbo].[fn_getgptime](cast (replace(datecollected, '/', '-') + ' ' + a.utctime datetime))              img      inner join          tloc b on a.filename = b.filename     order          b.idloc  

the date column in prompt table has datatype of float. utctime , datecollected both varchar(20)

the error is:

msg 242, level 16, state 3, line 274
conversion of varchar data type datetime data type resulted in out-of-range value.

here function:

[dbo].[fn_getgptime] (@utctime datetime) returns varchar(50) begin     return (datepart (weekday, @utctime) - 1) * 86400 ---day             + datepart (hour, @utctime) * 3600 ---hour             + datepart (minute, @utctime) * 60 ----minutes             + datepart (second, @utctime) ---second             + (datepart (millisecond, @utctime)) * 0.001 ---ms             + (datepart (microsecond, @utctime)) * 0.000001 ---us             + 16 ----leap seconds end; 

to idea of data itself:

how fix issue?

your error message mean 2 different things: have non-convertible data in cells, or field's data not convertible datetime @ all.

you can use try_convert instead of convert figure out is. solve problem if have few unusable values (i.e. bad data); you'll nulls bad data , conversion data. if overall conversion never going work you'll nulls , you'll know isn't few bad values.

another thing try converting float numeric before converting datetime. find float formatted data awful conversions , converting numeric can remove many issues. you'd have convert(datetime, convert(numeric(18,2), utctime))