Case Statement in SQL giving error

Question

I am trying to write a query which will give output as MONTH, YEAR.

When I write :

 select CAST( tbl.MONTH as varchar(2) ) + ', ' + CAST ( tbl.YEAR as varchar(4) ) as [DATE] 
 from TABLE as tbl

I get the output as

    1,2014
    4,2014 (depending upon the input)

But, Now, I want to replace 1 with JAN, 4 with APRIL 12 with DEC

So, I tried to write a case statement as :

SELECT 
    case when tbl.MONTH ='1' 
    then ('JAN' + ', ' + CAST ( tbl.YEAR as varchar(4) )) as [DATE1]  
from TABLE as tbl

and this gives syntax error. Can anyone tell me, what I should be doing ?

Based on the use of square braces, I'm removing the MySQL tag. — Gordon Linoff, Nov 03 '14 at 16:06

score 6 · Accepted Answer · answered Nov 03 '14 at 16:06

6

The case needs an end:

SELECT (case when tbl.MONTH = '1' then 'JAN' + ', ' + CAST(tbl.YEAR as varchar(4) ))
        end) as [DATE1]
from TABLE tbl;

If the values are being stored as numbers, then don't put single quotes around the constant value.

answered Nov 03 '14 at 16:06

Gordon Linoff

1,242,037
58
646
786

score 1 · Answer 2 · edited Nov 03 '14 at 16:43

1

You are missing the end, which is needed to close the case statement:

case
when tbl.MONTH ='1'
then ('JAN' + ', ' + CAST ( tbl.YEAR as varchar(4) ))
end
as [DATE1] 
from TABLE as tbl

edited Nov 03 '14 at 16:43

Pரதீப்

91,748
19
131
172

answered Nov 03 '14 at 16:06

Patrick Hofman

153,850
22
249
325

Pரதீப் · Answer 3 · 2014-11-03T16:45:56.327

Simple way is by using Datename Inbuilt function. No need of CASE statement to hardcode all the months

SELECT Datename(mm, tbl.[MONTH]) + ', '
       + CONVERT(VARCHAR(10), tbl.YEAR) AS [DATE]
FROM   TABLE AS tbl

or if you are using sql server 2012

SELECT Choose(tbl.[MONTH], 'Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec')
       + ', ' + CONVERT(VARCHAR(10), tbl.YEAR) AS [DATE]
FROM   TABLE AS tbl

score 0 · Answer 4 · edited May 23 '17 at 11:52

Similar to Pradeep's answer, but using a handy little DATENAME fix from https://stackoverflow.com/a/188390/405180, and trimming to first 3 chars:

SELECT UPPER(LEFT(Datename(MONTH , DateAdd( MONTH , tbl.[MONTH] , -1 )), 3)) + ', ' + CONVERT(VARCHAR(10), tbl.[YEAR]) AS [DATE]
FROM   TABLE AS tbl

TBH, I think from a performance point of view, you're better off using a CASE tbl.[MONTH] WHEN 1 THEN 'JAN' WHEN ... approach.

Case Statement in SQL giving error

4 Answers4