We help IT Professionals succeed at work.

How to get the national holidays in db2

huzefaq
huzefaq asked
on
1,536 Views
Last Modified: 2008-04-24
Hi Guys

I need to find out the total number of hours a store is open between two dates fromDate and toDate.  Is there a built in db2 function by which i can get all the national holidays. If not does anyone have data of national holidays for a customized table.

Any other ideas or any other help will be greatly appreciated

Thanks
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2011

Commented:
there is no supplied function/table generally available with db2 which supplies this information.

you normally have to establish your own holiday table for these purposes...

which version / os of DB2/UDB are you using...

how do you want to relate a national holiday?

e.g. in the UK Public Holidays   can normally be (legally) worked except Easter Sunday and Christmas Day...
and even then Petrol Stations etc are still allowed to open to serve travellers etc...

additional things to consider...
what type of store are you dealing with?
how do you want shift work to identifiy to the day?
are there local variations? e.g. in the UK , Scotland and Northern Ireland have different dates from some Holidays to England and Wales...

have you tried this site
http://www.bank-holidays.com/   

Author

Commented:
Thanks LowFatSpread

I have created a cusomized table with the following columns

STORE_ID,  DATE,     START TIME,       END TIME,    TYPE,   DAY    
For Holiday the data will be like this
10001         timestamp      timestamp       timestamp      HOLIDAY    SUNDAY
For regular working day hours the data will be like this
10001         null      timestamp       timestamp      workingDay    SUNDAY


Now in my java code what i need is that if the user provides me two dates(fromDate and toDate)  as timestamps, I need to calculate the total number of working hours.
In order to calculate the working hours between two dates I need to add the appropriate working hours for each working days(Monday, Tuesday etc) which I will get from the tables. Also I need to skip those days which are marked as holiday in the table.

Now would you know how I can accomplish this task in Java
what i will do is :
calculate the number of working days between the two dates (that is the number of days that are not saturday / sunday, or any other rule by which your store operates)

select from the holidays table the number of days that are holidays and are in the time frame between your dates

subtractd the second result from the first and multiply by the number of hours of work per day
CERTIFIED EXPERT
Top Expert 2011
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.