MONTH concatenation issues - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

MONTH concatenation issues

Author

Topic

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-01-15 : 15:12:34

Hi there


SELECT 
    CASE 
       WHEN MONTH(GETDATE()) <= 9 THEN '0' + CAST( MONTH(GETDATE()) AS CHAR(2)) 
       ELSE MONTH(GETDATE()-20) 
    END
[code]

result = 1

[code]

SELECT '0' + CAST( MONTH(GETDATE()) AS CHAR(2))

Result is what I want '01'

Why is it so? What am I missing here? I just want a way to tack a zero for months that are 1 digit. Is there a best way to do this maybe a function?

Thanks!

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-15 : 15:24:59

[code]select Right('0' + Convert(varchar(2), Month(getdate())), 2)[/code]

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-15 : 15:29:18

quote:
Originally posted by russell

select Right('0' + Convert(varchar(2), Month(getdate())), 2)

Even I thought abt the same, but put it inside the CASE statement..

select 
case when MONTH(GETDATE()) <= 9
then Right('0' + Convert(varchar(2), Month(getdate())), 2)
else MONTH(GETDATE()-20) 
END

still returns 1 instead of '01'...beats me.

vijayisonly
Master Smack Fu Yak Hacker

1836 Posts

Posted - 2010-01-15 : 15:33:15

Ok..even though Russell's solution should take care of your problem...
the problem seems to be with the ELSE part..since a datetime value is returned in the ELSE part, the character value form the THEN part is getting changed I believe,

This works fine..

select 
case when MONTH(GETDATE()) <= 9
then Right('0' + Convert(varchar(2), Month(getdate())), 2)
else Convert(varchar(2), Month(getdate()))
end

however this does not work,

select 
case when MONTH(GETDATE()) <= 9
then Right('0' + Convert(varchar(2), Month(getdate())), 2)
else MONTH(GETDATE()-20) 
END

Maybe someone else can explain this behavior better.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-15 : 15:43:58

quote:
Originally posted by vijayisonly

the problem seems to be with the ELSE part..since a datetime value is returned in the ELSE part, the character value form the THEN part is getting changed I believe

I think that is exactly what is going on. The right way to handle it is to cast/convert the entire result, not just parts of it:


SELECT Right('0' +
	Convert(varchar(2), 
	CASE 
	WHEN MONTH(GETDATE()) <= 9 THEN Month(getdate())
	ELSE Month(getdate()-20)
	END
	)
	, 2
)

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-01-15 : 16:06:38

Thanks y'all!
OK I guess I am not going crazy!
weird ain't it? But the logic should be going to first CASE statement so why would the ELSE affect it. could this be an issue with setting those SET values like ANSI_NULL and all

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-15 : 16:08:51

The origin example makes no sense to me...
Assume that getdate() gives 20101001:
then the else comes up and subtracts 20 days from date.
That gives a date in september and then the month is equal to 9 again...

Maybe you can tell us what you want to do and we can provide a better way.

No, you're never too old to Yak'n'Roll if you're too young to die.

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-01-15 : 16:09:17

look at this junk

select
case when MONTH(GETDATE()) <= 9
then '033' + Convert(varchar(2), Month(getdate()))
else MONTH(GETDATE())
end
select
case when MONTH(GETDATE()) <= 9
then '022' + Convert(varchar(2), Month(getdate()))
else MONTH(GETDATE())
end

select
case when MONTH(GETDATE()) <= 9
then '022' + Convert(varchar(2), Month(getdate()))
else Convert(varchar(2), Month(getdate()))
end
I think sql decides for you by going bottom up on the select and forcing the default of all THENs to the datatype of ELSE.
weird

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-01-15 : 16:14:23

webfred

yes it does not make any sense because that was just an example I put out there. I was also trying to test out double digit months.

thanks!

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-15 : 16:28:33

and always think about it: case-expression should always give the same datatype in all when / then / else.
anything other is nonsense.

No, you're never too old to Yak'n'Roll if you're too young to die.

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-15 : 16:32:52

quote:
Originally posted by webfred

case-expression should always give the same datatype in all when / then / else.
anything other is nonsense.

Exactly.

That's what I was trying to say above. You put it better

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2010-01-15 : 16:40:07

all nonsense put into consideration what actually goes on in SQL? how does it decide in case one is not a perfect coder and mistakenly puts a different datatype..that never happens of course.

<><><><><><><><><><><><><><><><><><><><><><><><><>
If you don't have the passion to help people, you have no passion

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2010-01-15 : 16:44:42

Mistakes happen. Nothing can prevent that.

Any expression should always return the same data type. I showed an example above how to make sure.

How MSSQL decides is with an implicit conversion. Better for YOU to decide with an explicit one

Kristen
Test

22859 Posts

Posted - 2010-01-16 : 05:04:32

"and always think about it: case-expression should always give the same datatype in all when / then / else.
anything other is nonsense."

Not sure I agree with that exactly.

SQL will choose a datatype, for the result of the CASE, that all the individual CASEs can be implicitly converted to.

You have some CHAR "Right('0' + Convert(varchar(2), Month(getdate())), 2)" and some INT "MONTH(GETDATE()-20) ", so SQL will attempt to implicitly convert to INT in this instance.

Thus when the CHAR datatype CASE is executed the answer is actually implicitly cast to INT - i.e. without the leading "0" in the CHAR string.

Kristen
Test

22859 Posts

Posted - 2010-01-16 : 05:31:35

Example:


SELECT CASE WHEN 1=1 THEN CONVERT(money, 1.0) ELSE CONVERT(datetime, '20000101') END
GO
SELECT CASE WHEN 1=1 THEN CONVERT(money, 1.0) ELSE NewID() END
                                                       
1900-01-02 00:00:00.000
(1 row(s) affected)

Server: Msg 257, Level 16, State 51, Line 1
Implicit conversion from data type uniqueidentifier to money is not allowed. Use the CONVERT function to run this query.

I didn't know there was an implicit conversion from MONEY to DATETIME, but there we go!

EDIT: I checked BOL - there is an implicit conversion from MONEY to DATETIME (can someone eplain when I would need that please? Wallstreet crash perhaps?

)

But this gives error as there is no implicit conversion from MONEY to VARCHAR (can someone exaplin why not? seems entirely sensible to me ...)


SELECT CASE WHEN 1=1 THEN CONVERT(money, 1.0) ELSE CONVERT(varchar(10), '20000101') END

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-01-16 : 08:45:37

Hello Kristen,
yes you're right when you say
SQL will choose a datatype, for the result of the CASE, that all the individual CASEs can be implicitly converted to.
But that is the point.
When you have the chance to code it clean then you would never trust SQL to make it by implicit conversion.
Am I right?

No, you're never too old to Yak'n'Roll if you're too young to die.

Kristen
Test

22859 Posts

Posted - 2010-01-16 : 08:56:35

I do wish there was a "Warnings Mode" that pointed out silly things - like LINT does for C / JS languages.

If I forget an ORDER BY I may get the rows in, say, Clustered Index order, and if that's the order I intended then I'll think its working OK.

Then one day (the one with a Blue moon!) SQL will have half the rows it needs in memory, start retrieving those for my query, and then get the rest from disk and the order will be wrong

I also have no intention what so ever of comparing two fields in a WHERE or JOIN that are of different types. I will ALWAYS use a cast. Unless I forget

. Some sort of STRICT mode, or a warning, would be very helpful.

I just cannot imagine wanting to implicitly cast a MONEY as a DATETIME (who decided THAT was a good idea, eh?). Tell me I'm a twit when I do it .. that would be fine by me! ... I guarantee I will only ever do it by accident ...

I expect you know, but its the same for IsNull() and Coalesce() - by "the same" I mean they make some choices about implicit cast if the parameters are of different datatypes, but by "the same" I do not mean that IsNull() and Coalesce() use the same rules for implicit cast

that would be too easy!! for my money, the implicit cast rules for IsNull() are broken, and thus I never use it and always use Coalesce() instead.

Ho!Hum!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-01-18 : 02:40:56

<<
for my money, the implicit cast rules for IsNull() are broken, and thus I never use it and always use Coalesce() instead.
>>

I too prefer coalesce

Isnull() sounds that it should return a boolean result just like isdate()

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources