Custom Date Column

Question

Hi

i have question

i want to create column "hrdate" its starts next month from 21 of each month and ends in 20
Could you help me with SQL query

Answer 1

-- Declare table with computed columns  
Declare @Sample Table(  
  TheDate date,   
  Month As DateName(month, TheDate),   
  HRMonth As DateName(month, Case When Day(TheDate) <= 20 Then TheDate Else DateAdd(Month, 1, TheDate) End)  
  );  
  
-- Load dates in table  
;With cte As  
(Select Cast('20220901' As date) As TheDate  
Union All  
Select DateAdd(day, 1, TheDate) From cte  
Where TheDate <= '20221022')  
Insert @Sample(TheDate) Select TheDate From cte;  
  
-- Check that computed columns have desired values  
Select TheDate, Month, HRMonth From @Sample;  

Tom

Answer 2

Try something like this:

select [day in month], [month],  
    lower(datename(month, dateadd(month, iif([day in month] > 20, 1, 0), cast('2000-' + [month] + '-1' as date)))) as hrmonth  
from MyTable