Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

how to concatenate o as perfix in case statment ? Expand / Collapse
Author
Message
Posted Thursday, May 9, 2013 3:09 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, May 26, 2014 6:02 AM
Points: 93, Visits: 195
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
Post #1450946
Posted Thursday, May 9, 2013 3:43 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 6,719, Visits: 13,824
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1450964
Posted Thursday, May 9, 2013 4:07 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, May 26, 2014 6:02 AM
Points: 93, Visits: 195
ya i agree but thats what my requirment .. any other way to over come this ?
Post #1450982
Posted Thursday, May 9, 2013 4:12 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 6,719, Visits: 13,824
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
Post #1450985
Posted Thursday, May 9, 2013 11:17 PM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Monday, May 26, 2014 6:02 AM
Points: 93, Visits: 195
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
Post #1451415
Posted Friday, May 10, 2013 1:48 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Friday, September 12, 2014 9:43 AM
Points: 6,719, Visits: 13,824
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
Exploring Recursive CTEs by Example Dwain Camps
Post #1451458
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse