Insert Month and Day only

Question

I am currently making a computerized payroll system and I'm having a problem with what query I should use to check if the Employee is present during a Holiday.

What I did was I created this query:

CREATE TABLE Holidays
(
      id INT PRIMARY KEY IDENTITY,
      holidayName VARCHAR(55),
      holidayDate DATE <---- This is my problem
);

CREATE TABLE EmployeeHolidays
(
     id INT PRIMARY KEY IDENTITY,
     employeeId INT FOREIGN KEY REFERENCES Employees(id),
     holidayId INT FOREIGN KEY REFERENCES Holidays(id),
     workingDate DATE,
     employeeTimeIn TIME,
     employeeTimeOut TIME
);

Now the problem lies within inserting the DATE in Holidays.holidayDate because I must insert the Year even though I only need the month to do a conditional statement for the EmployeeHolidays using C#.

What approach should I use for this problem? Should I just make a dummy year as an input for the Holidays.holidayDate? Should I make the Holidays.holidayDate a VARCHAR and just parse it as a DATE data type along with the current year? What queries should I use for this approach?

I am curious why `holidayDate` is not supposed to have the Year part. — muratgu, Aug 15 '13 at 04:25
You could create a function `IsHoliday` and remove the holiday table all together the hardest part I found was computing easter. Or this could all be part of a larger Calendar table that has an `IsHoliday`, `IsWeekEnd` etc which would seem useful for this type of application — T I, Aug 17 '13 at 10:33
Lots of holidays--President's Day, Ramadan, Thanksgiving--change dates every year. Having static "model holidays" might not be the best approach. In similar situations, I have createed a stored procedure that would generate the holidays for a given year (if they did not already exist in the table), and then I would match date against date. That worked out fine. It's way more efficient to use, because you can use straight SQL join logic to check whether a day is a holiday. — Curt, Aug 17 '13 at 15:15
FWIW, i've had the exact same problem and ended up creating a Calendar table which included a WorkingDay flag with a reason field. I personally allow payroll to manage holiday's but there really aren't so many (my company is in US and only has off on the most common holidays) that this needs to be automated. It's turned out to be *incredibly* convenient and simplifies practically all of my reports. When working with these types of systems, you will often find yourself building reports around a Calendar. — Zeph, Aug 17 '13 at 17:13

score 2 · Answer 1 · answered Aug 15 '13 at 04:26

With dummy years you will run into problems such as 29th Feb not being allowed depending on the dummy year used, although a 29 Feb might exist in the current year if it's a leap year.

I would recommend you store the actual full holiday date with year, if you don't use the year in your calculations then that's your business, but why store some other value?

score 2 · Answer 2 · edited Jun 20 '20 at 09:12

Since in most countries, date holidays change from year to year, due to weekends you should have the date in full date format.

further more, I think that you should store have denormalized Calendar table.

With columns such as:

Date
number of days since given start date (eg. 1-1-1900)
of working days given start date (eg. 1-1-1900)
Week of Year
Month month
Day part
DayOfWeek
Quarter #
first day of the month
first day of the quarter
is Weekend
is holiday (+ description)

once you fill with such a table you will easily make date math operations with best performance. a sample for feeding such a table is here

Note that if you need to support more than one country, you'll probably need to add a CountryCode column to hold calendars for each country.

score 1 · Answer 3 · answered Aug 15 '13 at 04:24

1

You can store day and month of holiday as separate fields of int type. Then when querying you can use Month and Day functions.

answered Aug 15 '13 at 04:24

user1429899

288
5
15

score 0 · Answer 4 · answered Aug 15 '13 at 04:29

For create date use function:

CREATE FUNCTION dbo.MyDate(@M INT, @D INT)
  RETURNS DATE
AS
BEGIN  
  RETURN DATEADD(MONTH, @M - 1, (DATEADD(DAY, @D - 1, 0)))
END  

SELECT dbo.MyDate(2, 10)

or with current year:

CREATE FUNCTION dbo.MyDate(@M INT, @D INT)
  RETURNS DATE
AS
BEGIN  
  RETURN DATEADD(YEAR, YEAR(GETDATE()) - 1900, DATEADD(MONTH, @M - 1, (DATEADD(DAY, @D - 1, 0))))
END

Bogdan Sahlean · Answer 5 · 2013-08-17T10:10:22.680

I would use a DATE column with a CHECK constraint thus:

CREATE TABLE Holidays
(
    ...
    HolidayDate DATE NOT NULL,
    CONSTRAINT CK_Holidays_VerifyYearOfHolidayDate CHECK( YEAR(HolidayDate)=1 )
);

Reasons:

You get MONTH and DAY validation. Otherwise, if I would use a SMALLINT column with four digits (ex. MMDD, MM=two digits for month and DD=two digits for day). Also, I would have to add another constraints to validate MM (1<= MM <=12), DD (1<= DD <= 31) and to validate the correlation between MM and DD (ex. January has 31 days but February has 28/29 days, etc.)
I can use date functions DAY, MONTH, DATEPART, etc. If I would use a SMALLINT column then I would use / and % operators in order to get month and day (ex. 1231 / 100 = 12 and 1231 % 100 = 31).

I would define a function which takes a year and a HolidayDate as parameters and returns the full year:

CREATE FUNCTION dbo.HolidayDateWithYear(@YYYY SMALLINT, @HolidayDate DATE)
RETURNS DATE
WITH SCHEMABINDING    
AS
BEGIN
RETURN DATEADD(YEAR,@YYYY,@HolidayDate);
END;

Insert Month and Day only

5 Answers5

number of days since given start date (eg. 1-1-1900)

of working days given start date (eg. 1-1-1900)