How to get current month and year of the database as a macro in SQL server?

Question

I want to get the current month and year of the system into a macro variable and keep using that macro variable to create related new variables. The purpose is that if I run my script every month, I will have a new macro variable for each month. I know how to achieve this in SAS:

%let month = %sysfunc(today(),monyy5.);
%let Updated = Product_&month;

So far, I think in SQL server I can use GETDATE() to get the current date and then use CONVERT to convert in my desired format. I am not sure how the rest of it will be done. Any help/advice/suggestions/thoughts much appreciated.

Answer 1

Use the DATEPART and/or DATENAME functions. If you want to reuse, you can create a function to return a string.

DECLARE @Updated VARCHAR(20)
-- numeric month
SET @Updated = 'Product_' + CONVERT(VARCHAR, DATEPART(MONTH, GETDATE())) + CONVERT(VARCHAR, DATEPART(YEAR, GETDATE()))
PRINT @Updated

Product_112018

-- 3 letter month
SET @Updated = 'Product_' + CONVERT(VARCHAR, LEFT(DATENAME(MONTH, GETDATE()), 3)) + CONVERT(VARCHAR, DATEPART(YEAR, GETDATE()))
PRINT @Updated

Product_Nov2018

• T.H.: That sounds like the way to go but when I implement this I get the error : Incorrect syntax near the keyword 'PRINT'. I looked up the syntax for Print and my input falls into one of the desired datatype of CHAR, NCHAR, VARCHAR or NVARCHAR. I can't figure out why the error. Did it happen to you as well?
• T.H.: Apologies, I was missing a ). It works.