I am creating a function in SQL Server to validate if given date is a Christmas day or new year's day. Please kindly advise if following code is OK:

Declare @paramDate date
....
IF Month(@paramDate) = 12 and DAY(@paramDate) = 25
     or Month(@paramDate) = 1 and DAY(@paramDate) = 1
BEGIN
   ....

Above is the simplest way I can think of regardless or timezone and date format

CodePudding user response：

I would create a table-valued function like this, rather than what I suspect is a scalar function based on the syntax in your question. Your checks are fine, I don't think they can be any simpler and still be intuitive, but another way to test for specific dates is to compare to DATEFROMPARTS():

CREATE FUNCTION dbo.IsSpecificHoliday
(
  @d date
)
RETURNS table WITH SCHEMABINDING
AS
  RETURN 
  (
      SELECT IsSpecificHoliday = CASE @d
        WHEN DATEFROMPARTS(YEAR(@d), 12, 25) THEN 1
        WHEN DATEFROMPARTS(YEAR(@d), 1,  1) THEN 1
        ELSE 0 END
  );

Sample usage:

CREATE TABLE dbo.Dates(TheDate date);

INSERT dbo.Dates(TheDate) VALUES
  ('19701214'),('19991225'),('20400101');

SELECT d.TheDate, f.IsSpecificHoliday
  FROM dbo.Dates AS d 
  CROSS APPLY dbo.IsSpecificHoliday(d.TheDate) AS f;

Results:

• Example db<>fiddle

You could also always consider a calendar table (for this and so many other purposes) and have a calculated or pre-populated column there like IsSpecificHoliday and simply join to that on the date column.

CodePudding user response：

You can use the implicit conversion in concert with the right() function

Example

Declare @date date = '2021-12-25'

Select case when right(@date,5) in ('12-25','01-01') then 1 else 0 end

With an explicit conversion

Declare @date date = '2021-12-25'

Select case when convert(varchar(5),@date,101)  in ('12/25','01/01') then 1 else 0 end