Get the date for custom day but current month in sql-CodePudding

I am working on some reporting module, where I need to implement the logic which gets a date as below cases -

My table :-

Id	Day
1	8
2	14
3	22
4	29

Now I have to write a query to get result as below -

Case 1- If current date (GETDATE()) is 2022-9-5 00:00:00.000

result
2022-9-8 00:00:00.000
2022-9-14 00:00:00.000
2022-9-22 00:00:00.000
2022-9-29 00:00:00.000

Case 2- If current date (GETDATE()) is 2022-9-16 00:00:00.000

result
2022-10-8 00:00:00.000
2022-10-14 00:00:00.000
2022-9-22 00:00:00.000
2022-9-29 00:00:00.000

Note : The query should work with any month / year.

CodePudding user response：

An IF ELSE is probably what you need

https://learn.microsoft.com/en-us/sql/t-sql/language-elements/if-else-transact-sql?view=sql-server-ver16

So if the day is greater than the day in the other table, add one month to the date.

CodePudding user response：

select case when datepart(day, getdate()) <= day then dateadd(month, datediff(month, 0, getdate()), day-1) 
            else dateadd(month, 1, dateadd(month, datediff(month, 0, getdate()), day-1)) end as result
from   t

result
2022-10-08 00:00:00.000
2022-10-14 00:00:00.000
2022-09-22 00:00:00.000
2022-09-29 00:00:00.000

Fiddle

CodePudding user response：

Some DATEADD willhep, as you first need to know, the first daty of the next month and then you can add the das from your table

The moth seledcted will be determined if the day of the Selct run is smaller than the day in the table

CREATE TABLE table1
    ([Id] int, [Day] int)
;
    
INSERT INTO table1
    ([Id], [Day])
VALUES
    (1, 8),
    (2, 14),
    (3, 22),
    (4, 29)
;

4 rows affected

SELECT getdate()

(No column name)
2022-09-20 18:55:21.917

SELECT
  DATEADD(DAY, [Day] -1,DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) 
  (CASE WHEN DAY(GETDATE()) < [Day] THEN 0 ELSE 1 END), 0))
FROM  table1

(No column name)
2022-10-08 00:00:00.000
2022-10-14 00:00:00.000
2022-09-22 00:00:00.000
2022-09-29 00:00:00.000

fiddle