By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,756 Members | 1,745 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,756 IT Pros & Developers. It's quick & easy.

how to extract first business date of each month

P: 4
Dear All,
Does anyone know how to extract first business date of each month in a DB2 SQL?


many many thanks
Dec 4 '07 #1
Share this Question
Share on Google+
2 Replies


docdiesel
Expert 100+
P: 297
Hi,

the function DayOfWeek() gives you an integer, with min=1 (sunday) and max=7 (saturday). If you define working days as Mo-Sa, this gives you the first working day on the month (1st or 2nd):

Expand|Select|Wrap|Line Numbers
  1. select 
  2.   ( 15 - DayOfWeek( '2007-10-01' ) ) / 7
  3. from
  4.   sysibm.sysdummy1
With the 1st day of month as parameter ('...-01'), the result is 2 for a month starting on sunday, while every other case results in 1. Does this fit your needs?

Regards,

Bernd
Dec 4 '07 #2

docdiesel
Expert 100+
P: 297
P.S.

if working with a column myday of type date in a table mytable, containing any kind of date, this should help:

Expand|Select|Wrap|Line Numbers
  1. select
  2.   ( 15 - (DayOfWeek( myday - (Day(myday) - 1) days) ) ) / 7
  3. from
  4.   myschema.mytable ;
Add ... + ( 2 * ( DayOfWeek( myday - (Day(myday) - 1) days) / 7) ) if business days is Mo-Fr in your definition. This results in 3 if 1st of month is saturday, 2 if it's sunday and 1 for every other weekday.

Regards,

Bernd
Dec 5 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.