how to concatenate o as perfix in case statment ?

Question

how to concatenate o as perfix in case statment ?

ravi@sql

SSCrazy

Points: 2121
More actions
May 9, 2013 at 3:09 am

#293128

Hi All,
select Case when MONTH(GETDATE())=1
then 12
when LEN(MONTH(GETDATE()))=2 then MONTH(GETDATE())
else right('0'+ convert(varchar(2), MONTH(GETDATE())),4) end
In the above query else statment will come it as to come with zero(0) as prefix. AM expecting 05 as result . but am getting only 5 . zero is not considering as varchar.
Please let me know the solution for this ?::(
Regards,
Ravi@sql

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

ravi@sql (5/9/2013)
Hi All,
select Case when MONTH(GETDATE())=1
then 12
when LEN(MONTH(GETDATE()))=2 then MONTH(GETDATE())
else right('0'+ convert(varchar(2), MONTH(GETDATE())),4) end
In the above query else statment will come it as to come with zero(0) as prefix. AM expecting 05 as result . but am getting only 5 . zero is not considering as varchar.
Please let me know the solution for this ?::(
Regards,
Ravi@sql

Data type precedence:

SELECT CASE

WHEN MONTH(GETDATE()) = 1 THEN '12'

WHEN LEN(MONTH(GETDATE())) = 2 THEN CAST(MONTH(GETDATE()) AS VARCHAR(2))

ELSE RIGHT('0'+ convert(varchar(2), MONTH(GETDATE())),2)

END

INT has a higher data type precedence than VARCHAR, so each choice in the CASE was implicitly cast to INT. Avoiding mixing data types in CASE expressions is good practice.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ravi@sql SSCrazy Points: 2121 More actions · Answer 2

ya i agree but thats what my requirment .. any other way to over come this ?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

ravi@sql (5/9/2013)
ya i agree but thats what my requirment .. any other way to over come this ?

CASE can only return one data type.

An ordinary column can only have one data type (ignore geography and other exotic types for the moment).

What data type do you want your output to be? What's wrong with VARCHAR(2)?

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

ravi@sql SSCrazy Points: 2121 More actions · Answer 4

Hi All,

Thank you for your valuble reply .. finally am able to over come my prob .. here is the query

Declare @date Datetime='2013-06-16 00:00:00.000'

select REPLACE(Convert(Varchar(25),CASE WHEN MONTH(@date)=1 THEN 12

WHEN (LEN(MONTH(@date))=2 AND MONTH(@date)<>10) THEN MONTH(@date)-1

ELSE

'-' + CONVERT(VARCHAR(5),MONTH(@date)-1 ) END),'-','0')

Regards,

Ravi@sql

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

ravi@sql (5/9/2013)
Hi All,
Thank you for your valuble reply .. finally am able to over come my prob .. here is the query
Declare @date Datetime='2013-06-16 00:00:00.000'
select REPLACE(Convert(Varchar(25),CASE WHEN MONTH(@date)=1 THEN 12
WHEN (LEN(MONTH(@date))=2 AND MONTH(@date)<>10) THEN MONTH(@date)-1
ELSE
'-' + CONVERT(VARCHAR(5),MONTH(@date)-1 ) END),'-','0')
Regards,
Ravi@sql

This is nicer:

RIGHT('0'+CAST(MONTH(DATEADD(MONTH,-1,TheDate)) AS VARCHAR(2)),2)

- because you can tell what it does at a glance. Here's a little test setup to demonstrate that it works:

SELECT TheDate,

MonthAsString1 = REPLACE(Convert(Varchar(25),

CASE

WHEN MONTH(TheDate)=1 THEN 12

WHEN (LEN(MONTH(TheDate))=2 AND MONTH(TheDate)<>10) THEN MONTH(TheDate)-1

ELSE '-' + CONVERT(VARCHAR(5),MONTH(TheDate)-1 )

END

),'-','0'),

MonthAsString2 = RIGHT('0'+CAST(MONTH(DATEADD(MONTH,-1,TheDate)) AS VARCHAR(2)),2)

FROM (

SELECT TheDate = '2013-01-01 00:00:00.000' UNION ALL

SELECT '2013-02-01 00:00:00.000' UNION ALL

SELECT '2013-03-01 00:00:00.000' UNION ALL

SELECT '2013-04-01 00:00:00.000' UNION ALL

SELECT '2013-05-01 00:00:00.000' UNION ALL

SELECT '2013-06-01 00:00:00.000' UNION ALL

SELECT '2013-07-01 00:00:00.000' UNION ALL

SELECT '2013-08-01 00:00:00.000' UNION ALL

SELECT '2013-09-01 00:00:00.000' UNION ALL

SELECT '2013-10-01 00:00:00.000' UNION ALL

SELECT '2013-11-01 00:00:00.000' UNION ALL

SELECT '2013-12-01 00:00:00.000'

) d

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden