SQLserving SQLserving - 3 years ago 101
SQL Question

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

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)
select
ROW_NUMBER() over (order by b.IDLoc),
[dbo].[fn_GetGPtime](cast (replace(DateCollected, '/', '-') + ' ' + a.UTCTime as datetime))
from
Img a
inner join
Tloc b on a.Filename = b.filename
order by
b.IDLoc


The
Date
column in prompt table has a datatype of
float
.
UTCTime
and
DateCollected
are both
varchar(20)


The error is:


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.


Here is the function:

[dbo].[fn_GetGPtime] (@UTCtime datetime)
returns varchar(50)
AS
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 get an idea of the data itself:

enter image description here

How do I fix this issue?

Answer Source

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))

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download