How not to manually put in the expressions in the "like" and "where" the desired month?

Question

Hello. I need help. Suppose i have such query.

/****** Script for SelectTopNRows command from SSMS  ******/
  SELECT  [month_id]
,[MDM_Key]
,[subgroup_id]
,[sale_count]
,[prop_sale_price]
,[MS_AUDIT_TIME]
FROM [Pricing].[dbo].[v_MODEL_ELAST10_3Y]
where month_id like ('%09%')
order by month_id

format of month_id

month_id (YYYYMM)

202211

202208

202212

202206

202306

I need to get data 3 months ahead of the current one.
Is it possible to somehow automate the system time in ssms 17?
I will try to describe in detail the essence of my question
  For example, if we imagine that today is June (202306), then from the sales history table I need only September sales (they are for 10 years), so I put like 09 to get September sales of past years. That is 3 months more. That is, from the current month, you need to get the sales history for the month that is 3 months ahead of the current month, June is the 6th month in order, and 3 months more than it is September, so we take the sales history for September
If it is August (202308), then 3 months ahead it is November, so November sales of previous years should be taken from the sales history.
However, I don't want to manually change the like expression every new month like ('%09%'),like ('%10%') like ('%11%') etc...
Is it possible to somehow automate the system time in ssms? For example, somehow write a request so that if it’s August at system time, then something like like(sys.time+3month ahead ) but only in the format in which the month_id field is (YYYYMM)

Similarly with the condition where. System time, but only plus 2 months ahead something like this

 /****** Script for SelectTopNRows command from SSMS  ******/
  SELECT  [month_id]
,[MDM_Key]
,[subgroup_id]
,[sale_count]
,[prop_base_price_prc]
FROM [Pricing].[dbo].[MODEL_ELAST]
where month_id =sys.time +2 month ahead

Is it possible to specify the conditions I need in the ssms 17 environment.

Thank you for your help.

Answer 1

You can use GETDATE() to get the current date and add 3 months for the desired future date. Then convert the future date to a ISO 8601 date string (YYYYMMDD format) and extract the 2-digit month for use in a LIKE expression:

SELECT  
	 [month_id]
	,[MDM_Key]
	,[subgroup_id]
	,[sale_count]
	,[prop_base_price_prc]
FROM [Pricing].[dbo].[MODEL_ELAST]
WHERE month_id LIKE '%' + SUBSTRING(CONVERT(char(8), DATEADD(month, 3, GETDATE()), 112), 5, 2) + '%'
ORDER BY month_id;

Answer 2

Hi @warlock

Try this query:

SELECT [month_id]
	  ,[MDM_Key]
	  ,[subgroup_id]
	  ,[sale_count]
	  ,[prop_base_price_prc]
FROM [Pricing].[dbo].[MODEL_ELAST]
WHERE RIGHT(month_id,2) = DATEPART(month,DATEADD(month, 3, GETDATE()))
ORDER BY month_id;

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.