Copy link to clipboard
Copied
I need to figure out of a date falls on a holiday...
such as memorial / labor day or thanksgiving, easter, etc.
these holidays float based on week of months or day of week... I'm sure there is code out there to do this
I'd hate to reinvent the wheel
thx
Copy link to clipboard
Copied
Copy link to clipboard
Copied
You might look into any Java libraries that do this, and leverage those. Here's similar questions from Stack Overflow related to Java:
-Carl V.
Copy link to clipboard
Copied
OT: Hello Wolf,
here my try to answer because of your " WolfShade 23.12.2015 16:33
What the hell is going on, now? I've been trying to respond to a thread in the CF forum, and at first it keeps telling me "could not post - you may have been logged out" (which, clearly, I wasn't).
And now I'm getting "Cannot post! Check back in 24 hours."
If you can read this, all is ok, with my post at least.
Hans-Günter
Copy link to clipboard
Copied
jpmyob wrote:
I need to figure out of a date falls on a holiday...
such as memorial / labor day or thanksgiving, easter, etc.
these holidays float based on week of months or day of week... I'm sure there is code out there to do this
I'd hate to reinvent the wheel
I understand. But I bet you can do this yourself. It might take you less time than searching for that wheel. In fact, I shall now give you an example, just off the top of my head.
Create the table publicHoliday in your database. It should consist of the 3 columns, holidayID [INTEGER], holidayDescription [VARCHAR] and holidayDate [DATETIME]. The column holidayID is just an integer which orders the dates.
Suppose you are interested in the period from 2015 to 2100. Then write down all the public holidays from that period into the table, starting with the earliest one. So, your table might begin as follows
holidayID | holidayDescription | holidayDate
1 New Year 2015 2015-01-01
2 ... ...
Now, insert more holidays into your table, in order of date. You will find this site helpful:
Year 2015 Calendar – United States
To get a new set of holidays, simply change the year in the URL, leaving everything else the same.
Your custom tag may return the public holidays corresponding to a given set of dates (More bang for the user's buck). The calling page will be something like this
publicHolidayFinder.cfm
<cfset dt = arrayNew(1)>
<cfset dt[1] = createdate(2017,1,16)>
<cfset dt[2] = createdate(2021,11,25)>
<cf_publicHoliday dateValues="#dt#">
<cfdump var="#foundHolidays#">
The custom tag:
publicHoliday.cfm
<cfparam name="attributes.dateValues" type="array" default="#arrayNew(1)#">
<cfparam name="variables.findHoliday" type="query" default="#queryNew("")#">
<cfset listOfDates = arrayToList(attributes.dateValues)>
<!--- Requirement: there must be at least one date --->
<cfif arrayLen(attributes.dateValues) GT 0>
<cfquery name="findHoliday" datasource="cfmx_db">
select *
from publicholiday
where holidayDate in (#listOfDates#)
</cfquery>
</cfif>
<cfset caller.foundHolidays = findHoliday>
Copy link to clipboard
Copied
I've been giving this some more thought. I really wish I had more time to give to it, as I'd love to create my very first CFC to donate to CFLIB and other repos. Maybe in the future.
For most holidays (New Years Eve/Day; Christmas; Independence Day; etc.), a set date makes it pretty easy.
For some holidays (Thanksgiving, Memorial Day, Labor Day; etc.), it would be slightly more complex as you'd have to check for a particular instance - Thanksgiving is always the fourth Thursday of November.
But for Easter, it would be a PITA - Easter Sunday is the first Sunday after the first full moon after the spring equinox. So you'd have to have a function to determine the first full moon AFTER the spring equinox, then get the first Sunday after that. And the spring equinox is not always the same date; sometimes it's the 21st, sometimes the 22nd, rarely the 20th or 23rd, and these can all change based upon LEAP YEAR. (SMH)
Like I said.. perhaps in the not-too-distant future I'll have time to sit down to write it. But, for now, I'm up to my elbows in work.
V/r,
^_^
Copy link to clipboard
Copied
But for Easter...... Use the Gauss Calculation
public DateTime EasterDate(int Year)
{
// Gauss Calculation
int Month = 3;
// Determine the Golden number:
int G = Year % 19 + 1;
// Determine the century number:
int C = Year / 100 + 1;
// Correct for the years who are not leap years:
int X = ( 3 * C ) / 4 - 12;
// Mooncorrection:
int Y = ( 8 * C + 5 ) / 25 - 5;
// Find sunday:
int Z = ( 5 * Year ) / 4 - X - 10;
// Determine epact(age of moon on 1 januari of that year(follows a cycle of 19 years):
int E = ( 11 * G + 20 + Y - X ) % 30;
if (E == 24) {E++;}
if ((E == 25) && (G > 11)) {E++;}
// Get the full moon:
int N = 44 - E;
if (N < 21) {N = N + 30;}
// Up to sunday:
int P = ( N + 7 ) - ( ( Z + N ) % 7 );
// Easterdate:
if ( P > 31 )
{
P = P - 31;
Month = 4;
}
return new DateTime(Year, Month, P);
}
Copy link to clipboard
Copied
This is not so much a tag issue as it is a data issue. The following is an MS SQL Script that will generate a DATE DIMENSION table commonly found in Data Warehouses. It will create 18,628 records that will provide you with all the date parts you can use with a unixtimestamp (To the day) as the primary key from Jan 1, 1970 to Dec 31, 2020. It also contains all the US holidays and isolates weekends Perhaps this will help you. Once you have the data implace you can write an easy function to determine if a specific date is a holiday or if you are only interested in holidays -- have this data return only holidays and source that data in some serialized format where it can be parsed w/o a server side process. Or...... just use the rules I use below to determine if a specific date is a holiday and write a function that mimics this SQL script at run-time using date parts of provided date object. Note: UnixTimestamps are calculated from UTC (0hr Offset). To ensure accuracy of the date you are evaluating, you would want to convert a local date object to a UTC date to account for the offset
-- /* THIS SCRIPT TAKES ABOUT 6 SECONDS TO RUN *\--
SET DATEFIRST 7
GO
IF EXISTS(SELECT * FROM sys.tables WHERE name='CAPI_DATE_DIMENSION')
DROP TABLE [dbo].[CAPI_DATE_DIMENSION]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CAPI_DATE_DIMENSION](
[UTS_ID] [bigint] NOT NULL,
[CALENDAR_DATE] [date] NULL,
[CALENDAR_YEAR] [int] NULL,
[QUARTER] [int] NULL,
[QUARTER_DESCRIPTION] [varchar](10) NULL,
[CALENDAR_MONTH] [int] NULL,
[MONTH_SHORT_NAME] [varchar](10) NULL,
[MONTH_LONG_NAME] [varchar](30) NULL,
[WEEK_IN_MONTH] [int] NULL,
[WEEK_IN_YEAR] [int] NULL,
[IS_WEEKEND] [bit] NULL,
[IS_WORK_DAY] [bit] NULL,
[DAY_OF_MONTH] [int] NULL,
[DAY_OF_YEAR] [int] NULL,
[DAY_OF_WEEK] [int] NULL,
[DAY_LONG_NAME] [varchar](10) NULL,
[DAY_SHORT_NAME] [varchar](5) NULL,
[IS_HOLIDAY] [bit] NULL,
[HOLIDAY_NAME] [varchar](35) NULL,
CONSTRAINT [PK_CAPI_DATE_DIMENSION] PRIMARY KEY CLUSTERED
(
[UTS_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET ANSI_NULLS ONGO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[UNIX_TIMESTAMP]') AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' ))
DROP FUNCTION [dbo].[UNIX_TIMESTAMP]
GO
CREATE FUNCTION [dbo].[UNIX_TIMESTAMP] (
@ctimestamp datetime
)
RETURNS bigint
AS
BEGIN
/* Function body */
DECLARE @return bigint
SELECT @return = DATEDIFF(SECOND,{d '1970-01-01'}, @ctimestamp)
return @return
END
GO
DECLARE @DT_START datetime = '1-1-1970', @DT_END datetime = '12-31-2020', @TOTAL_DAYS int, @I int = 0
SELECT @TOTAL_DAYS = DATEDIFF(DAY, @DT_START, @DT_END)
WHILE @I <= @TOTAL_DAYS
BEGIN
INSERT INTO CAPI_DATE_DIMENSION (UTS_ID)
SELECT CAST(dbo.UNIX_TIMESTAMP(DATEADD(DAY, @i, @DT_START)) as bigint)
DECLARE @THIS_DATE DATE = CAST(DATEADD(d, @i, @dt_start) as DATE)
UPDATE [dbo].[CAPI_DATE_DIMENSION]
SET [CALENDAR_DATE] = @THIS_DATE
,[CALENDAR_YEAR] = YEAR(@THIS_DATE)
,[QUARTER] = DATEPART(QUARTER, @THIS_DATE)
,[QUARTER_DESCRIPTION] = 'Q' + CAST(DATEPART(QUARTER, @THIS_DATE) as char(1)) + ' ' + CAST(YEAR(@THIS_DATE) as char(4))
,[CALENDAR_MONTH] = DATEPART(MONTH, @THIS_DATE)
,[MONTH_SHORT_NAME] = CASE DATEPART(MONTH, @THIS_DATE)
WHEN 1 THEN 'Jan' WHEN 2 THEN 'Feb' WHEN 3 THEN 'Mar'
WHEN 4 THEN 'Apr' WHEN 5 THEN 'May' WHEN 6 THEN 'June'
WHEN 7 THEN 'July' WHEN 8 THEN 'Aug' WHEN 9 THEN 'Sep'
WHEN 10 THEN 'Oct' WHEN 11 THEN 'Nov' WHEN 12 THEN 'Dec'
END
,[MONTH_LONG_NAME] = DATENAME(MONTH, @THIS_DATE)
,[WEEK_IN_MONTH] = CASE
WHEN DATEPART(DAY, @THIS_DATE) BETWEEN 1 AND 7 THEN 1
WHEN DATEPART(DAY, @THIS_DATE) BETWEEN 8 AND 14 THEN 2
WHEN DATEPART(DAY, @THIS_DATE) BETWEEN 15 AND 21 THEN 3
WHEN DATEPART(DAY, @THIS_DATE) BETWEEN 22 AND 28 THEN 4
ELSE 5
END
,[WEEK_IN_YEAR] = DATEPART(WEEK, @THIS_DATE)
,[IS_WEEKEND] = CASE DATEPART(weekday, @THIS_DATE) WHEN 1 THEN 1 WHEN 7 THEN 1 ELSE 0 END
,[DAY_OF_MONTH] = DATEPART(d, @THIS_DATE)
,[DAY_OF_YEAR] = DATEPART(dy, @THIS_DATE)
,[DAY_OF_WEEK] = DATEPART(WEEKDAY, @THIS_DATE)
,[DAY_LONG_NAME] = DATENAME(WEEKDAY, @THIS_DATE)
,[DAY_SHORT_NAME] = LEFT(DATENAME(WEEKDAY, @THIS_DATE),3)
WHERE [UTS_ID] = CAST(dbo.UNIX_TIMESTAMP(DATEADD(DAY, @i, @DT_START)) as bigint)
SET @I = @I + 1
END
GO
-- Fixed Holidays by specific date, such as July 4th, but if on a weekend would force either the previous Friday or
-- following Monday to be the holiday.
UPDATE [dbo].[CAPI_DATE_DIMENSION]
SET [IS_HOLIDAY] = 1, [HOLIDAY_NAME] = 'New Year''s Day' -- - January 1st
WHERE ([CALENDAR_MONTH] = 12 AND [DAY_OF_MONTH] = 31 AND [DAY_OF_WEEK] = 6) OR -- Friday 12/31 when 1/1 is Saturday
([CALENDAR_MONTH] = 1 AND [DAY_OF_MONTH] = 2 AND [DAY_OF_WEEK] = 2) OR -- Monday 1/2 when 1/1 is Monday
([CALENDAR_MONTH] = 1 AND [DAY_OF_MONTH] = 1 AND [DAY_OF_WEEK] BETWEEN 2 AND 6) -- 1/1 when 1/1 is Monday through Friday
GO
-- Independance Day - July 4th – Or if 7/4 falls on a Saturday, then observe on Friday;
-- if it falls on a Sunday, then observe on Monday
UPDATE [dbo].[CAPI_DATE_DIMENSION]
SET [IS_HOLIDAY] = 1, [HOLIDAY_NAME] = 'Independance Day'
WHERE
([CALENDAR_MONTH] = 7 AND [DAY_OF_MONTH] = 3 AND [DAY_OF_WEEK] = 6) OR
([CALENDAR_MONTH] = 7 AND [DAY_OF_MONTH] = 5 AND [DAY_OF_WEEK] = 2) OR
([CALENDAR_MONTH] = 7 AND [DAY_OF_MONTH] = 4 AND [DAY_OF_WEEK] BETWEEN 2 AND 6)
GO
-- American Veterans Day - November 11th – Or if 11/11 falls on a Saturday, then observe on Friday;
-- if it falls on a Sunday, then observe on Monday
UPDATE [dbo].[CAPI_DATE_DIMENSION]
SET [IS_HOLIDAY] = 1, [HOLIDAY_NAME] = 'Veterans Day'
WHERE
([CALENDAR_MONTH] = 11 AND [DAY_OF_MONTH] = 12 AND [DAY_OF_WEEK] = 2) OR
([CALENDAR_MONTH] = 11 AND [DAY_OF_MONTH] = 10 AND [DAY_OF_WEEK] = 6) OR
([CALENDAR_MONTH] = 11 AND [DAY_OF_MONTH] = 11 AND [DAY_OF_WEEK] BETWEEN 2 AND 6)
GO
-- Christmas - December 24th and 25th - Or if one or the other falls on a Saturday, then observe on Friday;
-- if it falls on a Sunday, then observe on Monday
UPDATE [dbo].[CAPI_DATE_DIMENSION]
SET [IS_HOLIDAY] = 1, [HOLIDAY_NAME] = 'Christmas'
WHERE
([CALENDAR_MONTH] = 12 AND [DAY_OF_MONTH] = 26 AND [DAY_OF_WEEK] = 2) OR
([CALENDAR_MONTH] = 12 AND [DAY_OF_MONTH] = 24 AND [DAY_OF_WEEK] = 6) OR
([CALENDAR_MONTH] = 12 AND [DAY_OF_MONTH] = 25 AND [DAY_OF_WEEK] BETWEEN 2 AND 6)
GO
-- Fixed Holidays, Mondays through Fridays.
UPDATE [dbo].[CAPI_DATE_DIMENSION]
SET [IS_HOLIDAY] = 1, [HOLIDAY_NAME] = 'Martin Luther King Jr. Birthday' -- third Monday in January
WHERE [CALENDAR_MONTH] = 1 AND [WEEK_IN_MONTH] = 2 AND [DAY_OF_WEEK] = 2
GO
UPDATE [dbo].[CAPI_DATE_DIMENSION]
SET [IS_HOLIDAY] = 1, [HOLIDAY_NAME] = 'Presidents Day' -- third Monday in February
WHERE [CALENDAR_MONTH] = 2 AND [WEEK_IN_MONTH] = 2 AND [DAY_OF_WEEK] = 2
GO
UPDATE [dbo].[CAPI_DATE_DIMENSION]
SET [IS_HOLIDAY] = 1, [HOLIDAY_NAME] = 'Labor Day' -- first Monday of September
WHERE [CALENDAR_MONTH] = 9 AND [WEEK_IN_MONTH] = 1 AND [DAY_OF_WEEK] = 2
GO
UPDATE [dbo].[CAPI_DATE_DIMENSION]
SET [IS_HOLIDAY] = 1, [HOLIDAY_NAME] = 'Columbus Day' --- second Monday in October
WHERE [CALENDAR_MONTH] = 10 AND [WEEK_IN_MONTH] = 2 AND [DAY_OF_WEEK] = 2
GO
UPDATE [dbo].[CAPI_DATE_DIMENSION]
SET [IS_HOLIDAY] = 1, [HOLIDAY_NAME] = 'Thanksgiving Day' -- fourth Thursay in November plus the adjoining Friday
WHERE ([CALENDAR_MONTH] = 11 AND [WEEK_IN_MONTH] = 4 AND [DAY_OF_WEEK] = 5) OR
([CALENDAR_MONTH] = 11 AND [WEEK_IN_MONTH] = 4 AND [DAY_OF_WEEK] = 6)
GO
UPDATE [dbo].[CAPI_DATE_DIMENSION] SET [IS_HOLIDAY] = 0, [HOLIDAY_NAME] = '' WHERE [IS_HOLIDAY] IS NULL
GO
UPDATE [dbo].[CAPI_DATE_DIMENSION] SET [IS_WORK_DAY] = CASE WHEN [IS_WEEKEND] = 0 AND [IS_HOLIDAY] = 0 THEN 1 ELSE 0 END
GO
In the event you wish to have a calendar table that accompanies the date dimension table -- this is a good start
/****** Object: Table [dbo].[CAPI_CALENDAR] Script Date: 12/29/2015 9:45:32 AM ******/ SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[CAPI_CALENDAR](
[CALENDAR_ID] [uniqueidentifier] NOT NULL,
[CALENDAR_TYPE] [varchar](35) NOT NULL,
[CALENDAR_CLASS] [varchar](35) NULL,
[EVENT_NAME] [varchar](35) NOT NULL,
[EVENT_DETAILS] [varchar](1024) NOT NULL,
[EVENT_CATEGORY] [varchar](35) NOT NULL,
[UTS_START] [bigint] NOT NULL,
[UTS_END] [bigint] NULL,
[IS_PUBLISHED] [bit] NULL,
[SURROGATE_KEY] [varchar](37) NULL,
CONSTRAINT [PK_CAPI_CALENDAR] PRIMARY KEY CLUSTERED
(
[CALENDAR_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[CAPI_CALENDAR] ADD CONSTRAINT [DF_CAPI_CALENDAR_CALENDAR_ID] DEFAULT (newid()) FOR [CALENDAR_ID]
GO
ALTER TABLE [dbo].[CAPI_CALENDAR] ADD CONSTRAINT [DF_CAPI_CALENDAR_UTS_END] DEFAULT ((0)) FOR [UTS_END]
GO
ALTER TABLE [dbo].[CAPI_CALENDAR] ADD CONSTRAINT [DF_CAPI_CALENDAR_IS_PUBLISHED] DEFAULT ((0)) FOR [IS_PUBLISHED]
GO
ALTER TABLE [dbo].[CAPI_CALENDAR] WITH CHECK ADD CONSTRAINT [FK_CAPI_CALENDAR_CAPI_METADATA] FOREIGN KEY([CALENDAR_TYPE])
REFERENCES [dbo].[CAPI_METADATA] ([META_KEY])
GO
ALTER TABLE [dbo].[CAPI_CALENDAR] CHECK CONSTRAINT [FK_CAPI_CALENDAR_CAPI_METADATA]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Unique identifier and primary key constraint' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CAPI_CALENDAR', @level2type=N'COLUMN',@level2name=N'CALENDAR_ID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Mandatory metadata key value that determines the type of calendar object this object represents. The Type taxonomy can be determined referencing PROTOTYPE_KEY: CALENDAR_TYPE' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CAPI_CALENDAR', @level2type=N'COLUMN',@level2name=N'CALENDAR_TYPE'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Optional metadata key value that determines the classification of the calendar object this object represents. Common classes are usually determined by the type of object.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CAPI_CALENDAR', @level2type=N'COLUMN',@level2name=N'CALENDAR_CLASS'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'The event name should be a fairly short and concise name that identifies the premise of the event. Note: this value will show up in interface components with little real estate to spare.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CAPI_CALENDAR', @level2type=N'COLUMN',@level2name=N'EVENT_NAME'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Event detail provides space to provide more details to the specifics of the event.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CAPI_CALENDAR', @level2type=N'COLUMN',@level2name=N'EVENT_DETAILS'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'An event category can be used to define additional isolation levels to the data in this object.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CAPI_CALENDAR', @level2type=N'COLUMN',@level2name=N'EVENT_CATEGORY'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Unix time stamp indicating the beginning of an event.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CAPI_CALENDAR', @level2type=N'COLUMN',@level2name=N'UTS_START'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Unix time stamp indicating the end of an event' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CAPI_CALENDAR', @level2type=N'COLUMN',@level2name=N'UTS_END'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Boolean value to determine if this event is public information or is private.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CAPI_CALENDAR', @level2type=N'COLUMN',@level2name=N'IS_PUBLISHED'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'This surrogate key can be used for a variety of purposes. It''s intent is to provide storage for a unique
identifier of another record you wish to with associate calendar event(s) that establishes ownership.' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CAPI_CALENDAR', @level2type=N'COLUMN',@level2name=N'SURROGATE_KEY'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Calendar holds information about a scheduled event. A calendar object forms the basis for populating a schedule of events or even a Calendar' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'CAPI_CALENDAR'
GO