Finding the last day (eg. sunday) of the month 2

[mulias]

I am trying to build a query for finding the last day (eg. in this example I need to find the last Sunday of the month).

Anybody done it before / have example?

Thanks!

 

[karluk]

The following query will return the last Sunday of the month.

select last_day(sysdate) - to_char(last_day(sysdate),'d') + 1 from dual;

June 30 happens to be a Sunday this year, but the query will work even if the last Sunday doesn't fall on the last day of the month.

 

[mulias]

Thanks Karluk!

This is very short and elegant. I tried to build multiple if statement and temp variable to store the last date and manipulate the next_date to get the last sunday (or any other date), but you solve it using one line of code

BTW, can we change the sunday into other day?

 

[karluk]

Calculating days other than Sunday is a little more complicated. The calculation will need the following table of offsets:

0 = Sat
1 = Fri
2 = Thu
3 = Wed
4 = Tue
5 = Mon
6 = Sun

Using this offset, perform the following:

select last_day(sysdate) - mod(to_char(last_day(sysdate),'d') + offset,7) from dual;

For example, to get the last Wednesday of the month:

select last_day(sysdate) - mod(to_char(last_day(sysdate),'d')+3,7) from dual;

The mod function is needed to deal with the two possibilities that the last day of the month can be either earlier or later in the week than the day you are trying to calculate.

 

[sem]

Karluk, you're happy man, because you work in default environment. In fact the result of aplying 'd' format mask depends on NLS_TERRITORY. In many countries the week starts from Monday. So a bit more complex but also more universal formula:

last_day(sysdate) - mod(7 + to_char(last_day(sysdate),'d')-<the day number you need in YOUR week>,7)

The less clear thing (7 + ) guarantees nonnegative result of mod() function.

If Wednesday is really the 4th day of the week the result is.

last_day(sysdate) - mod(7 + to_char(last_day(sysdate),'d')-4

For my country (as well as for many European countries) Wednesday is the third day so I need

last_day(sysdate) - mod(7 + to_char(last_day(sysdate),'d')-3

This formula need no offset table either.

The obvious consequence is getting the last day of the month the same as current (doesn't depend on nls):

last_day(sysdate) - mod(7 + to_char(last_day(sysdate),'d')-to_char(sysdate,'d')

 

[karluk]

Thanks, Sem. Now that you mention it I remember discussing this issue in thread186-166342, which concerned a very similar problem.

 

[sem]

In fact the main idea was to switch from IMHo less obvious
to_char(last_day(sysdate),'d') + offset
to a bit clearer
to_char(last_day(sysdate),'d') - daynumber.

 

[carp]

I don't know much about this NLS stuff - guess I'm a fortunate man too. But it seems to me that

select next_day(last_day(sysdate)-7,'Sunday') from dual

should do the trick. Substitute 'Monday', etc for the day you're interested in and it appears to work (at least for June 2002!).

 

[karluk]

That's right, Carp. Your solution is very close to the solution we came up with in the thread from last November. It also works without added complexity for other days of the week. I think the only added wrinkle we came up with in November is a solution that will probably work in non-English speaking countries. We replaced the literal &quot;Sunday&quot; with a calculation based on a fixed date that we know falls on a Sunday. It's a nice feature for portability, but the basic strategy remains the same as your solution.