0

The query should return the last week day of every month.For eg if the last week day of mar 2013 is Sunday, then query should display the 29 mar 2013(friday).

Thiyagu
  • 85
  • 3
  • 7
  • 2
    Can you post what you've tried so far? StackOverflow is about helping folks who've tried and failed to solve a problem; it's not about handing out homework answers. And what do you mean by "every month"? Oracle dates can range from 4712 BCE to 9999 ACE, so "every month" could mean over 176,000 months. I can give you this much: look into the Oracle [`LAST_DAY`](http://docs.oracle.com/cd/B28359_01/server.111/b28286/functions077.htm#i83733) function. – Ed Gibbs Jun 04 '13 at 14:09
  • Consider using a Calendar table. Have a look at this answer http://stackoverflow.com/a/5123255/472226 – Ronnis Jun 05 '13 at 11:12

2 Answers2

1

Try this:

select dte as last_day, dayofweek_abbr as original_dayofweek, 
dte - (decode(dayofweek, 7, 1, 1, 2, 0)) as last_weekday_america
from (
    select last_day(to_date(to_char(level, '09') || '2013', 'MMYYYY')) as dte,
    to_char(last_day(to_date(to_char(level, '09') || '2013', 'MMYYYY')), 'D') as dayofweek,
    to_char(last_day(to_date(to_char(level, '09') || '2013', 'MMYYYY')), 'DAY') as dayofweek_abbr
    from dual
    connect by level <= 12
)

Output:

LAST_DAY    ORIGINAL_DAYOFWEEK  LAST_WEEKDAY_AMERICA
1/31/2013   THURSDAY    1/31/2013
2/28/2013   THURSDAY    2/28/2013
3/31/2013   SUNDAY      3/29/2013
4/30/2013   TUESDAY     4/30/2013
5/31/2013   FRIDAY      5/31/2013
6/30/2013   SUNDAY      6/28/2013
7/31/2013   WEDNESDAY   7/31/2013
8/31/2013   SATURDAY    8/30/2013
9/30/2013   MONDAY      9/30/2013
10/31/2013  THURSDAY    10/31/2013
11/30/2013  SATURDAY    11/29/2013
12/31/2013  TUESDAY     12/31/2013

Note that if your nls_territory is different, you may have to tweak the decode.

tbone
  • 15,107
  • 3
  • 33
  • 40
0

Following up on what Ed's hint and tbone's code. Formats the date the way you want. The FMDAY formatter removes extra spaces after the day of the week. 

SELECT dte AS last_day, 
       dayofweek_abbr AS original_dayofweek, 
       to_char(dte - (decode(dayofweek, 7, 1, 1, 2, 0)),'DD MON YYYY(FMDAY)') AS     last_weekday_america
FROM (
      SELECT last_day(to_date(to_char(level, '09') || '2013', 'MMYYYY')) as dte,
             to_char(last_day(to_date(to_char(level, '09') || '2013', 'MMYYYY')), 'D') as dayofweek,
             to_char(last_day(to_date(to_char(level, '09') || '2013', 'MMYYYY')), 'DAY') as      dayofweek_abbr
      FROM dual
      CONNECT BY LEVEL <= 12
)
ExcessOperatorHeadspace
  • 318
  • 4
  • 17