How to implement this CASE Statement logic on my Date variable?

I am using SQL Server 2014 and I need to implement a specific CASE STATEMENT logic in my T-SQL query.

I need to create a new column (Period) in my output based on a column in the Table I am running the query against. This column will contain either “Peak” or “Off-Peak” based on the “StayDate” column in my Table.

The “StayDate” column is in this format (YYYY-MM-DD): 2020-12-25

And here is the Case Statement logic:

If the dates are between 21 December and 03 January, then it will be “Peak”, else “Off-Peak”.

My Case Statement looks as follows:

(CASE WHEN (DATEPART(DAY, [StayDate]) in (21,22,23,24,25,26,27,28,29,30,31,1,2,3) 
      AND DATEPART(MONTH, [StayDate]) in (12,1) ) THEN 'Peak'
      Else 'Off-Peak'
      End) as 'Period'

Although this works, I feel it is not the most elegant solution.

Is there a more efficient way of doing this?

Answer

If the dates are between 21 December and 03 January, then it will be “Peak”, else “Off-Peak”.

I don’t think your logic works because Dec 1 and Jan 24th are not peak. The correct logic would be:

(CASE WHEN MONTH(StayDate) = 12 AND DAY(StayDate) >= 21
      THEN 'Peak'
      WHEN MONTH(StayDate) = 1 AND DAY(StayDate) <= 3
      THEN 'Peak'
      ELSE 'Off-Peak'
 END) as Period