CODEWITHSUNDEEP
sql-servert-sqlisnull
user396123
user396123

Reputation: 69

T-SQL: combine current year and month from month column

I am trying to combine current year in the form of YYYY with the monthy from the month column. 

monthy      monthsname
------------------------
 1          January
 2          February
 3          March
 4          April
 5          May
 6          June
 7          July
 8          August
 9          September
10          October
11          November
12          December

Here is the output I am getting:

monthy  monthsname  month_number
------------------------------------
  5          May            NULL
  6          June           NULL
  8          August         NULL
  9          September      NULL
 10          October        NULL
 11          November       NULL
  1          January        201701
  2          February       201702
  3          March          201703
  4          April          201704
  7          July           201707
 12          December       201712

Instead of month_number being null, I was trying to do this:

isnull(ss.month_number, cast(convert(varchar(4), DATEPART(year, getdate())) as int) + right('0-- ' + cast(convert(varchar(2), monthy) as int), 2))

I am not getting the right output: I want something like 201705 for the first row record for month_number

Upvotes: 0

Views: 293

Answers (2)

MayowaO
MayowaO

Reputation: 370

Can you try this: 

 SELECT 
     CONVERT(VARCHAR(4),DATEPART(YEAR, GETDATE())) +  --year
     (  
        REPLICATE('0', 2 - LEN(CONVERT(VARCHAR(2), monthy)) ) + 
        CONVERT(VARCHAR(2), monthy) --monthy
     )

Upvotes: 0

Anthony Hancock
Anthony Hancock

Reputation: 931

Depending on what your datatype is, you might want to cast this but it's simply the year * 100 plus the month number to get the integer representation of the number you are trying to create.

ISNULL(month_number,YEAR(GETDATE())*100+monthy)

Upvotes: 1

Related Questions