CODEWITHSUNDEEP
sqlsql-serverstored-procedures
JVGBI
JVGBI

Reputation: 575

Format YearMonth to dutch monthname

I have the following YearMonth data:

201601
201602
201603

Etcetera.

I need to convert them to dutch month names, with format 'MMM'

So 201601 should become 'jan' 201602 should become 'feb'

Etc

I have the following code:

SELECT FORMAT(RIGHT(YearMonth,2),'MMM', 'nl-NL') as monthname

I am getting this error:

Argument data type varchar is invalid for argument 1 of format function.

What am I doing wrong ?

Upvotes: 1

Views: 1425

Answers (3)

Wes H
Wes H

Reputation: 4439

The error is because FORMAT does not accept a string for its first parameter. Using the RIGHT function implicitly converts the result into a string.

You can convert the output of RIGHT back into a number, but your statement would perform better if you do it with math only. 201710 % 100 will return just the remainder of 10.

Note the FORMAT command can be very slow when used across multiple rows. You'd get better performance changing the language for the session and using other date functions such as DATENAME.

Upvotes: 0

Michael Mairegger
Michael Mairegger

Reputation: 7301

If YearMonth is of type int then you can use following sql statement:

SELECT FORMAT(DATEFROMPARTS(201710 / 100, 201710 % 100, 1), 'MMM', 'nl-NL')

and the result is: okt

TODO change 201710 to YearMonth

Explanation

  • DATEFROMPARTS takes 3 parameters Year,Month,Day. The first parameter is calculated by dividing by 100: 201710 / 100 = 2017 (int / int = int). The second parameter is calculated using the modulo operator: 201710 % 100 = 10

Sample

CREATE TABLE #tempDates (YearMont int)

INSERT INTO #tempDates VALUES(201710)
INSERT INTO #tempDates VALUES(201711)
INSERT INTO #tempDates VALUES(201712)
INSERT INTO #tempDates VALUES(201801)

SELECT FORMAT(DATEFROMPARTS(YearMont / 100, YearMont % 100, 1), 'MMM', 'nl-NL') monthname FROM #tempDates

Result

-------------
| monthname |
|-----------|
| okt       |
| nov       |
| dec       |
| jan       |
-------------

Upvotes: 1

SqlZim
SqlZim

Reputation: 38023

If yearmonth is an integer, then you can convert it to a date first and then use format() like so:

create table t (yearmonth int)
insert into t values (201601),(201602),(201603)

select format(
    convert(date,convert(varchar(10),yearmonth*100+1))
    ,'MMM','nl-NL') as monthname
from t

rextester demo: http://rextester.com/NPPN83037

returns: 

+-----------+
| monthname |
+-----------+
| jan       |
| feb       |
| mrt       |
+-----------+

If yearmonth is a character type:

select format(
    convert(date,yearmonth+'01')
    ,'MMM','nl-NL') as monthname
from v

Upvotes: 0

Related Questions