Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


how to concatenate o as perfix in case statment ?


how to concatenate o as perfix in case statment ?

Author
Message
ravi@sql
ravi@sql
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 Visits: 347
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 ?:Sad

Regards,
Ravi@sql
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8923 Visits: 19005
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 ?:Sad

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
Exploring Recursive CTEs by Example Dwain Camps
ravi@sql
ravi@sql
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 Visits: 347
ya i agree but thats what my requirment .. any other way to over come this ?
ChrisM@Work
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8923 Visits: 19005
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
Exploring Recursive CTEs by Example Dwain Camps
ravi@sql
ravi@sql
SSC-Enthusiastic
SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)SSC-Enthusiastic (127 reputation)

Group: General Forum Members
Points: 127 Visits: 347
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
ChrisM@Work
SSCrazy Eights
SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)SSCrazy Eights (8.9K reputation)

Group: General Forum Members
Points: 8923 Visits: 19005
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
   Wink,'-','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
Exploring Recursive CTEs by Example Dwain Camps
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search