7 Replies Latest reply on Dec 29, 2015 1:55 PM by SemperVI

    custom tag or code to tell me if a date is a holiday (US).

    jpmyob

      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

        • 1. Re: custom tag or code to tell me if a date is a holiday (US).
          WolfShade Level 4

          Hello, jpmyob,

           

          There might be something at CFLIB.  If not, it shouldn't be too difficult to make your own UDF.  Just check the current date and compare it to various holidays (like Thanksgiving is always the fourth Thursday of November, etc.)

           

          HTH,

           

          ^_^

          • 2. Re: custom tag or code to tell me if a date is a holiday (US).
            Carl Von Stetten Adobe Community Professional & MVP

            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.

            • 3. Re: custom tag or code to tell me if a date is a holiday (US).
              BKBK Adobe Community Professional & MVP

              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>

              • 4. Re: custom tag or code to tell me if a date is a holiday (US).
                hans-g. Adobe Community Professional & MVP

                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

                • 5. Re: custom tag or code to tell me if a date is a holiday (US).
                  WolfShade Level 4

                  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,

                   

                  ^_^

                  • 6. Re: custom tag or code to tell me if a date is a holiday (US).
                    SemperVI Level 1

                    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 ON

                    GO

                     

                    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

                    • 7. Re: custom tag or code to tell me if a date is a holiday (US).
                      SemperVI Level 1

                      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);

                      }