I have gone through a bunch of posts here with this error and tried changing data types and used Convert but nothing seems to fix this. So I would like to ask for some help here. I will try to give as much info, but feel free to ask if its not enough.
This is where I am getting the error:
Insert into prompt(ID, Date)
ROW_NUMBER() over (order by b.IDLoc),
[dbo].[fn_GetGPtime](cast (replace(DateCollected, '/', '-') + ' ' + a.UTCTime as datetime))
Tloc b on a.Filename = b.filename
Msg 242, Level 16, State 3, Line 274
The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.
[dbo].[fn_GetGPtime] (@UTCtime datetime)
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
Your error message could mean two different things: that you have non-convertible data in some cells, or that field's data are not convertible to datetime at all.
You can use
try_convert instead of
convert to figure out which it is. It will solve your problem if you have a few completely unusable values (i.e. bad data); you'll get nulls for bad data and good conversion for good data. If the overall conversion is never going to work you'll get all nulls and you'll know it isn't just a few bad values.
Another thing you could try is converting from float to numeric before converting to datetime. I find that float formatted data are awful for conversions and converting to numeric can remove many issues. You'd have something like
convert(datetime, convert(numeric(18,2), UTCTime))