0

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.

0

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

  • 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. Nov 2 '18 at 12:37
  • Apologies, I was missing a ). It works. – T.H. Nov 2 '18 at 12:52

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.