CODEWITHSUNDEEP
sqlsql-serverdatetime
SQLserving
SQLserving

Reputation: 400

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?

Upvotes: 1

Views: 15075

Answers (3)

Ludovic Aubert
Ludovic Aubert

Reputation: 10528

What worked for me, solving this error on an input line such as

SELECT CAST(N'2003-12-01 14:20:47.000' AS DateTime) AS result

Msg 242 Level 16 ...

is the magic instruction:

SET DATEFORMAT ymd;

Upvotes: 0

Rominus
Rominus

Reputation: 1221

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

Upvotes: 3

Zohar Peled
Zohar Peled

Reputation: 82474

Use convert instead of cast. When using convert, you can specify the format of the string representing the date.
Once you've converted DateCollected to datetime, you can cast a.UTCTime to datetime and add them together:

Insert into prompt(ID,Date)  
select ROW_NUMBER() over (order by b.IDLoc),
[dbo].[fn_GetGPtime](convert(datetime, DateCollected, 101) + cast(a.UTCTime as datetime))
from Img a inner join Tloc b on a.Filename=b.filename
order by b.IDLoc

(assuming a.UTCTime is either varchar or time)

Upvotes: 1

Related Questions