Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 how to concatenate o as perfix in case statment ? Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, May 09, 2013 3:09 AM
 SSC Journeyman Group: General Forum Members Last Login: Sunday, November 10, 2013 9:21 PM Points: 91, Visits: 193
 Hi All,select Case when MONTH(GETDATE())=1then 12 when LEN(MONTH(GETDATE()))=2 then MONTH(GETDATE())else right('0'+ convert(varchar(2), MONTH(GETDATE())),4) endIn 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
Post #1450946
 Posted Thursday, May 09, 2013 3:43 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 9:57 AM Points: 6,295, Visits: 12,113
 ravi@sql (5/9/2013)Hi All,select Case when MONTH(GETDATE())=1then 12 when LEN(MONTH(GETDATE()))=2 then MONTH(GETDATE())else right('0'+ convert(varchar(2), MONTH(GETDATE())),4) endIn 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@sqlData 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 ShawFor 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 ModenExploring Recursive CTEs by Example Dwain Camps
Post #1450964
 Posted Thursday, May 09, 2013 4:07 AM
 SSC Journeyman Group: General Forum Members Last Login: Sunday, November 10, 2013 9:21 PM Points: 91, Visits: 193
 ya i agree but thats what my requirment .. any other way to over come this ?
Post #1450982
 Posted Thursday, May 09, 2013 4:12 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 9:57 AM Points: 6,295, Visits: 12,113
 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 ShawFor 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 ModenExploring Recursive CTEs by Example Dwain Camps
Post #1450985
 Posted Thursday, May 09, 2013 11:17 PM
 SSC Journeyman Group: General Forum Members Last Login: Sunday, November 10, 2013 9:21 PM Points: 91, Visits: 193
 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)-1ELSE '-' + CONVERT(VARCHAR(5),MONTH(@date)-1 ) END),'-','0')`Regards,Ravi@sql
Post #1451415
 Posted Friday, May 10, 2013 1:48 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 9:57 AM Points: 6,295, Visits: 12,113
 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)-1ELSE '-' + CONVERT(VARCHAR(5),MONTH(@date)-1 ) END),'-','0')`Regards,Ravi@sqlThis 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 ShawFor 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 ModenExploring Recursive CTEs by Example Dwain Camps
Post #1451458

 Permissions