CODEWITHSUNDEEP
sqlsql-servert-sqldate
sterrek
sterrek

Reputation: 57

Can't convert month number to name

I've the following line of SQL: 

SELECT CONCAT(CONCAT(MONTH('YEAR-MM-01'), CONCAT(' ', YEAR('YEAR-MM-01'))), '  |  ') AS period;

It retuns for example 3 2019. I want the 3 to be replaced with March

Any help would be welcome.

Upvotes: 2

Views: 137

Answers (6)

zedfoxus
zedfoxus

Reputation: 37129

Use something like this:

select 
    concat(
        datename(month, getdate()),
        ' ',
        year(getdate()),
        ' | '
    ) as period

If your output used to be 3 2019 |, your query would look like this to get March 2019 |:

select 
    concat(
        datename(month, cast('2019-03-01' as date)),
        ' ',
        year(cast('2019-03-01' as date)),
        ' | '
    ) as period

Upvotes: 2

John Cappelletti
John Cappelletti

Reputation: 82010

Just because nobody mentioned format()

To be clear, format() has some great functionality, but the performance can suffer. It should be used sparingly.

Example

Select format(GetDate(),'MMMM, yyyy | ')

Returns

April, 2019 |

Upvotes: 3

Salman Arshad
Salman Arshad

Reputation: 272396

Just format the date/datetime value using FORMAT function:

SELECT FORMAT(GETDATE(), 'MMMM yyyy')

Complete list of format specifiers is available here.

Upvotes: 1

Don
Don

Reputation: 1

Use DATENAME instead to get the name of the month.

SELECT CONCAT(DATENAME(mm,'YEAR-MM-01' ), ' ', YEAR('YEAR-MM-01'))

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271111

This works for your example:

select datename(month, cast(replace('1 ' + v.str, ' ', '-') as date)) + right(str, 5)
from (values ('3 2019')) v(str)

Upvotes: 1

Amir Molaei
Amir Molaei

Reputation: 3840

Using

datename(month, '2000/01/02')

You can get the month name:

select  concat(concat(datename(month,'YEAR-MM-01'),concat(' ',year('YEAR-MM-01'))),'  |  ') as period;

Upvotes: 2

Related Questions