Re: Oracle calendar teaser...

>I have a little teaser for y'all. I want to write an SQL script (not
>PL/SQL) that will tell me how many weekdays (Mon-Fri) there are between
>2 dates. Can this be done and if so how?
>
>Thanks in advance.
>
>Martin
>
>
>

[Quoted] Perhaps someone will post a tested SQL command to do it; I do not have SQL*PLUS at home, so I can't write an test this. However, I can describe one way; hopefully it will be close enough that you can fix it if it is not correct.

[Quoted] You can easily subtract the dates to get the number of days

select d2-d1 from dual;

[Quoted] Divide by 7 to get the number of weeks, then truncate to get the number of FULL weeks

[Quoted] select int((d2-d1)/7) from dual

[Quoted] Multiply by 5 to get workdays in each week

[Quoted] select 5 *int((d2-d1)/7) from dual

[Quoted] Now the tricky part - you need to get the fractional part of the week (this will add between 0 and 6 days). There is a date format code that returns the day of the week a day falls on, I think it is 'W'. Likewise, I don;t remember Oracle's convention, but suppose it is Sunday=0, Monday is 1,.. Saturday =6.

[Quoted] If d1 falls on Sunday, and d2 falls on Monday (ie d1 is before d2 in the week), we will add the number of days between, which is d2[Monday]-d1[Sunday] = 1 - 0 = 1

[Quoted] If d1 falls on Tuesday, and d2 falls on Monday (id d1 is later in the week than d2), we need to add from the last Tuesday to Saturday [when the week starts] and then from Sunday to Monday. This gives us 6[Saturday]- 2[Tuesday] + 1[Monday] - 0[Sunday] = 6 - d1 - d2.

Now you can put in a correction...
[Quoted] w1=int(to_char(d1,'W')) and w2= same thing for d2

decode(sgn(w2,w1),0,0,-1, 6-w1-w2,1,w2-w1)+(the stuff above)

[Quoted] I did this once a few years ago at a previous job; I don't remember if I got it in a single select statement or not. Hopefully, this will get you on the right track.

Dan Hekimian-Williams Received on Wed Nov 18 1998 - 06:32:11 CET