Function to check if a date is a Christmas day or New Years day

user_1233359 0
Viewed 139

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

tsql sql-server sql
2 Answers

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:

TheDate IsSpecificHoliday
1970-12-14 0
1999-12-25 1
2040-01-01 1

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.

user_613050

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
user_15700000
Related