Can anyone explain this?
-
I am having a problem that I am tearing my hair out about. I don't expect that anyone will be able to help me but I'm hoping someone has seen this before.
I have a SP that is failing. I know exactly the spot where it is failing - it is the following bit of code:
- CONVERT(smalldatetime,
- CAST(vg.CalYearID AS VARCHAR(4)) + '-' +
- RIGHT('0' + CAST(r.BGCancellationMonthID AS VARCHAR(2)),2) + '-' +
- RIGHT('0' + cast(r.BGCancellationDayID AS VARCHAR(2)),2)
- )
it fails with:
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.
This snippet of code is part of a derived table in an UPDATE statement.
Now here's the thing. If I take that UPDATE statement and run it in QA. It works fine. That is bizarre and I cannot understand it. There is nothing else going on in the SP that would cause it to be operating on any other data.
It gets worse though. I've been scratching my head about this all afternoon. Earlier on when I ran it I COULD get it to cause the same error running it in QA (no idea why...I can't reproduce that behaviour now). In fact I could run the derived table query containing the above code snippet and it would fail. I then copied and pasted EXACTLY the same code into another QA window and it ran successfully. I could switch between the 2 windows, running the same UPDATE against the same data on the same server from the same client - one of them errors, one of them works WHAT????
I swear I am not making this up. I have witnesses to prove it
I'm supposing there may be some hidden characters in there somewhere so I deleted the whole chunk of code and typed it in again by hand. This made no difference.
Has anyone ever seen ANYTHING like this before?
I'm going home now, possibly to find a piece of rope and a tall building. I hope there's a reply to this soon
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
Try
CONVERT(smalldatetime, CAST(vg.CalYearID AS VARCHAR(4)) + '-' + RIGHT('0' + CAST(r.BGCancellationMonthID AS VARCHAR(2)),2) + '-' + RIGHT('0' + cast(r.BGCancellationDayID AS VARCHAR(2)),2) + ' 00:00')Or possibly:
CONVERT(smalldatetime, CAST(vg.CalYearID AS VARCHAR(4)) + '-' + RIGHT('0' + CAST(r.BGCancellationMonthID AS VARCHAR(2)),2) + '-' + RIGHT('0' + cast(r.BGCancellationDayID AS VARCHAR(2)),2) + ' 00:00:00')I think that should work.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
For what its worth, I experienced the exact same behavior a few months ago. And, yes, my hair is still growing back out! The problem ended up being caused by the way SQL stores the code, and lines commented with the "--" comment syntax. Part of the code was being included in the comment. Here's a link to the thread...
http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=143935
I have no idea if it has anything to do with your situation, but who knows? I haven't taken the time to examine your code, or Jim's solutions. Just saw the behavior you are experiencing and remembered seeing the same thing.
Steve
-
I do have comments in the code (although they're not near the bit its complaining about) so I'll check this out tomorrow (I'm at home at the mo).
Thanks Steve.
I'll reply if and when it starts working.
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
Are there any potential NULLs involved ?
If yes, do you have different ANSI SET options in place that change how NULLs are dealt with ?
[Edit] - Also, why the hypens ? Without the hyphens, the date would be in unambiguous ANSI YYYYMMDD format. With the hyphens, you're opening the door to month/day position confusion, leading to out of range month values.
[Edit2] On the connection where it errors, replace with this:
- ISDATE(
- CAST(vg.CalYearID AS VARCHAR(4)) + '-' +
- RIGHT('0' + CAST(r.BGCancellationMonthID AS VARCHAR(2)),2) + '-' +
- RIGHT('0' + cast(r.BGCancellationDayID AS VARCHAR(2)),2)
- )
Look for the data values that cause ISDATE() to report zero.
-
The records I am updating are NULL prior to the update. Afterwards, any row that gets updated will have a non-NULL value. So, I can't see why this would be an issue. Certainly in the code snippet I posted at the top there are no NULLs involved.
Thanks for the suggestion though!
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
No, not the destination table. I'm asking about the columns vg.CalYearID, r.BGCancellationMonthID and r.BGCancellationDayID
Can any of these be NULL when you run this expression ? Also, see the 2 edits I made to my original reply.
-
You appear to be missing the third (Style) parameter from CONVERT. Also, consider using SET DATEFORMAT rather than relying on the server default. As a general rule, I try never to rely on defaults for anything - that way, you are proof against someone changing a setting without taking your application into account, portability is improved and it makes everything crystal clear for subsequent maintenance by somebody who may be totally new to your environment.
-
Hi,
No, as I said before "Certainly in the code snippet I posted at the top there are no NULLs involved.".
I like your other suggestions though...I'm trying them out now.
Thanks for the comments.
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
OK,
This is what I've done.
1. Cleared all the comments out of the SP. Still fails
2. Issued DBSS FREEPROCCACHE - still fails
3. Added + '00:00:00' - still fails
4. Took code out of syscomments - still fails
5. Stripped out everything except the derived table select - still fails
6. Added the style parameter (120) to CONVERT - IT WORKS!!!
And seeing as its Masters week... STEWART JOSLYN - YOU'RE THE MAN!!!!
This doesn't explain why when I ran the statement on its own it worked OK but frankly, I don't give a monkey's.
Never again will I leave the style parameter out of a CONVERT.
Thank you to everyone that posted a comment and an especially big thanks to Stewart.
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
Glad I could help.
You might look at Tools/Options/Connection in Query Analyzer. Is is using Regional Settings?
Microsoft defaults assume that everyone is American. That can cause problems if you are European! One of the reasons that I don't like relying on defaults!
-
I wonder if you cast it instead of converted it would it have worked?
Because cast is implicit conversion, convert is both implicit/explicit.
Just a thought, Jamie, if you're willing to expirement.
----------------
Jim P.A little bit of this and a little byte of that can cause bloatware.
-
Because you are converting from a date expressed as text which can be interpreted ambiguously, you still need SET DATEFORMAT YMD or whatever.
-
I can confirm that the error occurs when using CAST as well.
2 lessons learnt:
1. Use the third parameter when using CONVERT
2. Because you can specify the third parameter, use CONVERT instead of CAST.
Thanks all.
Jamie Thomson
http://sqlblog.com/blogs/jamie_thomson -
Did you try my initial suggestion, and strip out the hyphens, forming a standard, non-ambiguous YYYYMMDD date format ?
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply