June 5, 2014 at 4:31 am
Hi,
I have a lot of rows of hours, set up like this: 0745, 0800, 2200, 1145 and so on (varchar(5), for some reason).
These are converted into a smalldatetime like this:
CONVERT(smalldatetime, STUFF(timestarted, 3, 0, ':')) [this would give output like this - 1900-01-01 11:45:00]
This code has been in place for years...and we stick the date on later from another column.
But recently, it's started to fail for some rows, with "The conversion of a varchar data type to a smalldatetime data type resulted in an out-of-range value".
My assumption is that new data being added in is junk. If I query for these values and just list them (rather than adding a column to convert them also) that's fine, of course. I've checked all the stuffed (but not yet converted - so 11:45 rather than 1145) output to see if it ISDATE(), and it is. There are no times with hours > 23 or minutes greater than 59 either.
If I add the CONVERT in, we see the error message. But here's the oddity, if I place all of the rows into a holding table, and retry the conversion, there is no error. It's this last bit that is puzzling me. Plus I can't see any errors in the hours data that would cause a conversion problem.
I've put the whole of this into a cursor to try to trap the error rows too, but all processes fine. Why would it fail if NOT in a cursor?
Does anyone have any ideas on this?
Regards, Greg.
June 5, 2014 at 4:39 am
Do you filter out any data in the process?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 5, 2014 at 4:42 am
Nope, not filtering any rows.
June 5, 2014 at 4:48 am
What exactly do you mean with the "holding" table?
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 5, 2014 at 4:53 am
Just to make diagnostics easier, I inserted the hours values (1100, 1425 etc.) into a 1 field temp table - still varchar(5). I thought I'd see the same convert error if I processed against this table (but there was no error).
Neither is there an error if I use a cursor to step through the rows using the main query, and PRINT CONVERT(smalldatetime, STUFF(@holdtime, 3, 0, ':')) for each row.
But the error is there if I add CONVERT(smalldatetime, STUFF(@holdtime, 3, 0, ':')) as a column to the main query code.
It should all be the same data, and frankly, I can't see why it fails in the first place...
Regards, Greg.
June 5, 2014 at 4:55 am
I can get that exact error by doing something like this:
declare @sTime varchar(5) = ' 2359'
select convert(smalldatetime, stuff(@sTime, 3, 0, ':'))
So I'm wondering whether there might be some dodgy characters in there somewhere which are somehow dropped when you add in another step (weird, I know).
I think you need to find some examples of failing times and look at them closely.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
June 5, 2014 at 5:32 am
Yes, well good idea - but I've trimmed just in case, and displayed the string with [] around it, just to be sure that's not the problem.
I've tried to find the rows that fail, but can't. Err, that's the problem really...
June 5, 2014 at 5:34 am
greg.bull (6/5/2014)
Yes, well good idea - but I've trimmed just in case, and displayed the string with [] around it, just to be sure that's not the problem.I've tried to find the rows that fail, but can't. Err, that's the problem really...
If it is an invisible control character, you won't see it even if you put [] around it.
Maybe check the length of all the fields using LEN and DATALENGTH.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 5, 2014 at 6:05 am
OK will try, good idea.
I notice also that:
declare @sTime varchar(5) = ' 2359'
select isdate (@sTime)
returns 1! So not a useful check...
Greg
June 5, 2014 at 6:07 am
Nope, they are all 4 chars in length...
June 5, 2014 at 6:40 am
Dear All,
Thanks for this, I think I've found some non-valid times, and this is the problem. SQL error messages never lie!
Regards, Greg
June 5, 2014 at 6:50 am
greg.bull (6/5/2014)
Dear All,Thanks for this, I think I've found some non-valid times, and this is the problem. SQL error messages never lie!
Regards, Greg
Now I'm curious what went wrong of course 😀
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
June 5, 2014 at 7:38 am
I don't actually know as in my results set, there WERE no errors. So went back to basics and scanned the entire database for errors that failed the isdate() test. Then found a few and fixed em. My problem then goes away. The query shouldn't have hit these errors anyway, but still...it worked...
June 5, 2014 at 7:40 am
We never saw the actual query though.
It is possible that you filter those rows out somewhere (with a join or something like that).
However in some cases the query optimizer might do the conversion at the beginning of the query plan, instead of at the end.
This means SQL Server tries to do the conversion on rows it shouldn't be doing the conversion.
That's why I asked if there were any filters at the beginning of this thread.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply