April 16, 2008 at 1:31 pm
I know how to get around it, but I was curious if anybody know why this is happening.
select
case
when len(datepart(mm, getdate())) = 1 then '0'+convert(char(2), datepart(mm, getdate()))
else datepart(mm, getdate())
end
This currently of course returns a value of 4, but I was expecting it to return 04. After a bit of testing, I find that it seems to be doing an implicit conversion of '0' to int and then a math calculation first before the concatenation.
If you change the 0 to an alpha, it will error out with a conversion error.
Interestingly enough, if I change the value 0 to anything else like 1 or 10, etc. It will do the concatenation it correctly.
April 16, 2008 at 1:36 pm
I'm thinking it has to do with the fact that one branch of your case yields a Char(2), and the other, an integer. In order to remain consistent, the char(2) is being implicitly converted BACK to an integer.
Try this instead:
select
case
when len(datepart(mm, getdate())) = 1 then '0'+convert(char(2), datepart(mm, getdate()))
else convert(char(2),datepart(mm, getdate()))
end
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 16, 2008 at 1:43 pm
Interesting. I tried using:
select right(char(48) + datepart(mm, getdate()), 2)
and got "4", instead of "04". Would have thought that would do it, but was wrong.
On the other hand:
select right(char(48) + cast(datepart(mm, getdate()) as varchar(2)), 2)
worked. "04".
So did:
select right('0' + cast(datepart(mm, getdate()) as varchar(2)), 2)
and
select right(cast(0 as varchar(2)) + cast(datepart(mm, getdate()) as varchar(2)), 2)
I use the Right() string function instead of a case statement for these. It works no matter how many leading zeroes you want to deal with.
As for this:
select
case
when len(datepart(mm, getdate())) = 1
then cast(0 as varchar(2))+convert(varchar(2), datepart(mm, getdate()))
else datepart(mm, getdate())
end
Even with both conversions in it, still doesn't work.
I'm curious how you worked around it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 16, 2008 at 1:47 pm
Check out BOL for CASE and notice the following:
ELSE else_result_expression
Is the expression returned if no comparison operation evaluates to TRUE. If this argument is omitted and no comparison operation evaluates to TRUE, CASE returns NULL. else_result_expression is any valid expression. The data types of else_result_expression and any result_expression must be the same or must be an implicit conversion.
To make your statement work as intended, adjust the ELSE expression, as below:
select
case
when len(datepart(mm, getdate())) = 1 then '0'+convert(char(2), datepart(mm, getdate()))
else convert(char(2),datepart(mm, getdate()))
end
However, you can accomplish the same with one line of code:
select convert(char(2),getdate(), 1)
GL!
April 16, 2008 at 1:48 pm
Yep, Matt's right (as usual). There's an implicit conversion because of the "else" statement.
I tried this, to see what SQL would do:
select
case
when len(datepart(mm, getdate())) = 1
then cast(0 as varchar(2))+convert(varchar(2), datepart(mm, getdate()))
when 1 = 1 then 'aa'
else datepart(mm, getdate())
end
And got "4", no leading zero. I wonder what that will do in October?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 16, 2008 at 1:51 pm
Gsquared - you're still only converting one branch of the CASE statement.
As in -
select
case
when len(datepart(mm, getdate())) = 1
then cast(0 as varchar(2))+convert(varchar(2), datepart(mm, getdate())) --<--this is Char(2)
else datepart(mm, getdate()) --<--This is integer
end
When you have a case like this, you end up in one of those cases where SQL needs to try to figure out which of the two types to return. In this case - it chooses INT (according to the "data type precedence" rules)....
Data type precedence rules described here....(note that higher precedence =lower number in the article)
http://msdn2.microsoft.com/en-us/library/ms190309.aspx
Thanks to that - you have to CONVERT the ELSE statement too, or else you keep getting the INT.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 16, 2008 at 1:59 pm
SELECT CONVERT(CHAR(2), GETDATE(), 103)
N 56°04'39.16"
E 12°55'05.25"
April 16, 2008 at 2:01 pm
As for your original question:
It depends on which line of CASE statement get evaluated first.
Since the two lines returns different datatypes (line 1 varchar, line 2 int) the first line to return a result decides which datatype entire resultset will have.
N 56°04'39.16"
E 12°55'05.25"
April 16, 2008 at 2:02 pm
Peso (4/16/2008)
SELECT CONVERT(CHAR(2), GETDATE(), 103)
Format 103 has the Day first. You need to use formats 1, 101, 10 or 110 to get a 2 digit month as the first 2 characters.
April 16, 2008 at 2:04 pm
Matt, I think we cross-posted. See my prior cross-post to your cross-post of my first post. 🙂
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 16, 2008 at 2:10 pm
Peso (4/16/2008)
As for your original question:It depends on which line of CASE statement get evaluated first.
Since the two lines returns different datatypes (line 1 varchar, line 2 int) the first line to return a result decides which datatype entire resultset will have.
Sorry, but I don't think this is quite correct. Run the following example.
select
case
when len(datepart(mm, getdate())) = 1 then '05'+convert(char(2), datepart(mm, getdate()))
else convert(char(2),datepart(mm, getdate()))
end
select
case
when len(datepart(mm, getdate())) = 1 then '05'+convert(char(2), datepart(mm, getdate()))
else datepart(mm, getdate())
end
Notice that the first statement returns "054", because the ELSE expression is also CHAR. The second statement returns "54", indicating that the same expression is still evaluating to true, yet the result is converted to INT to match the ELSE expression.
Take a look at both my previous post with the BOL excerpt and Matt's with the link to data type precedence.
April 16, 2008 at 2:12 pm
GSquared (4/16/2008)
Matt, I think we cross-posted. See my prior cross-post to your cross-post of my first post. 🙂
yup! Looks like the issue is well in hand...It's one of those fun "whoa - this thread just blew up!" things...:D
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 16, 2008 at 2:28 pm
John Beggs (4/16/2008)
Peso (4/16/2008)
As for your original question:It depends on which line of CASE statement get evaluated first.
Since the two lines returns different datatypes (line 1 varchar, line 2 int) the first line to return a result decides which datatype entire resultset will have.
Hey wow. Thanks guys, this was driving me nuts a bit. Go figure, I never knew that about a case statement.
I knew there was a data precedence rule, but I didn't know how that was affecting it due to the else portion. I should have figure it still part of the case expression.
As to the poster who asked how I worked around it... Please don't laugh...
declare @Month varchar(2)
set @Month = datepart(mm, getdate())
select case
when len(@Month) = 1 then '0'+@Month
else @Month
end
No implicit conversion here! 🙂
I don't like
SELECT CONVERT(CHAR(2), GETDATE(), 103)
This only works if the you have a true datetime datatype. If you switch getdate() with say '2008/01/15', it would return 20.
April 16, 2008 at 2:32 pm
Matt Miller (4/16/2008)
GSquared (4/16/2008)
Matt, I think we cross-posted. See my prior cross-post to your cross-post of my first post. 🙂yup! Looks like the issue is well in hand...It's one of those fun "whoa - this thread just blew up!" things...:D
Actually, my standards for "exploding threads" have been changed by the ones where the Question of the Day is wrong. Like http://www.sqlservercentral.com/Forums/Topic464861-1181-18.aspx 🙂
This thread? Not so exploded.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 14 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply