How to write an expression that adds text month to numerical value?

Question

I have a report which returns number of doors sold in a month from 2 locations. The month is displayed as a value 1-12.... I would like to return 1-Jan, 2-Feb etc.

EDIT

I am using: datepart(MONTH, DeFactoUser.F_ST_Transaction.STTR_DATE) to get the month number, I have tried: datepart(MONTH, DeFactoUser.F_ST_Transaction.STTR_DATE) + '-' + datename(MONTH, DeFactoUser.F_ST_Transaction.STTR_DATE) to get the name appended to the number. I get an error Conversion failed when converting the nvarchar value 'September' to data type int.

Obviously doing something silly.... any clues would be good.

@GarethD Linked to a rather old solution. I suspect OP is on 2012 — John Cappelletti, Oct 16 '17 at 14:24
Even if using 2012+ I would still recommend using the most upvoted answer in the link (not the accepted answer). On a table with 500,000 rows of numbers 1-12, I found that `FORMAT(DATEFROMPARTS(1, i, 1),'M-MMM')` consistently took 2-3 times longer than `CONCAT(i, '-', LEFT(DATENAME(MONTH, DATEADD(MONTH, I, -1 )), 3))`. — GarethD, Oct 16 '17 at 15:31

score 2 · Answer 1 · answered Oct 16 '17 at 14:07

If your month column is a date you can use the DATENAME function.

If it is just a number you can still use DATENAME but simulate the date with DATEFROMPARTS e.g.

SELECT datename(MONTH,datefromparts(2017,1,1))

then just concatenate your number and the name with

concat(cast(yournum AS varchar(2)),'-',datename(MONTH,datefromparts(2017,1,1)))

score 1 · Answer 2 · answered Oct 16 '17 at 14:02

1

try this:

SELECT CAST(MONTH(GETDATE()) AS nvarchar(2))  +'-'+ CONVERT(varchar(3), GETDATE(), 100)

answered Oct 16 '17 at 14:02

Marian Nasry

821
9
22

Keith · Accepted Answer · 2017-10-16T14:10:30.143

1

You can use a case expression:

SELECT CASE WHEN [Month] = 1 THEN '1-Jan'
WHEN [Month] = 2 THEN '2-Feb'
WHEN [Month] = 3 THEN '3-Mar'
...
END AS [MonthName]

Alternative is using DATENAME:

SELECT CAST(MONTH(1) AS NVARCHAR(2))+'-'+LEFT(UPPER(DATENAME(MONTH,MONTH(1))),3)

This has been asked before: Convert Month Number to Month Name Function in SQL

If you do not want a case expression.

edited Oct 16 '17 at 14:10

answered Oct 16 '17 at 14:05

Keith

1,008
10
19

How to write an expression that adds text month to numerical value?

3 Answers3