OraFAQ Forum: SQL & PL/SQL » last day of first week in a month

Home » SQL & PL/SQL » SQL & PL/SQL » last day of first week in a month

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

last day of first week in a month [message #287501]

Wed, 12 December 2007 07:14

world.apps
Messages: 70
Registered: January 2007
Location: Hyderabad

Member

Hi,

Could you help me in finding the last day of first week in a month?

Thanks in advance

Report message to a moderator

Re: last day of first week in a month [message #287503 is a reply to message #287501]

Wed, 12 December 2007 07:17

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Depending on the original problem there is several possible answer.
Have a look at NEXT_DAY and TRUNC functions, for instance.

Regards
Michel

Report message to a moderator

Re: last day of first week in a month [message #287542 is a reply to message #287501]

Wed, 12 December 2007 10:25

amit.navapara
Messages: 5
Registered: December 2007
Location: Bangalore

Junior Member

Try This...

TO_CHAR(TRUNC(SYSDATE,'MONTH') + 6,'DAY')

in your logic.

Report message to a moderator

Re: last day of first week in a month [message #287546 is a reply to message #287542]

Wed, 12 December 2007 10:38

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Depends on what week means.
Is first week start with:
- first day of month
- standard (for your territory) first day of the first complete week in the month
- standard first day of the first week that contains at least 4 days in the month

Regards
Michel

Report message to a moderator

Re: last day of first week in a month [message #287631 is a reply to message #287501]

Thu, 13 December 2007 00:25

kir_ait
Messages: 198
Registered: November 2007
Location: Bangalore,India

Senior Member

Are you looking for this?

SQL> SELECT NEXT_DAY (TO_DATE ('01092007', 'ddmmyyyy') - 1, 'SAT')||' - '||
  2         TO_CHAR (NEXT_DAY (TO_DATE ('01092007', 'ddmmyyyy') - 1, 'SAT'), 'DAY') SEPTEMBER,
  3         NEXT_DAY (TO_DATE ('01102007', 'ddmmyyyy') - 1, 'SAT')||' - '||
  4         TO_CHAR (NEXT_DAY (TO_DATE ('01102007', 'ddmmyyyy') - 1, 'SAT'), 'DAY') OCTOBER,
  5         NEXT_DAY (TO_DATE ('01112007', 'ddmmyyyy') - 1, 'SAT')||' - '||
  6         TO_CHAR (NEXT_DAY (TO_DATE ('01112007', 'ddmmyyyy') - 1, 'SAT'), 'DAY') NOVEMBER,
  7         NEXT_DAY (TO_DATE ('01122007', 'ddmmyyyy') - 1, 'SAT')||' - '||
  8         TO_CHAR (NEXT_DAY (TO_DATE ('01122007', 'ddmmyyyy') - 1, 'SAT'), 'DAY') DECEMBER
  9    FROM DUAL;

SEPTEMBER             OCTOBER               NOVEMBER              DECEMBER
--------------------- --------------------- --------------------- ---------------------
01-SEP-07 - SATURDAY  06-OCT-07 - SATURDAY  03-NOV-07 - SATURDAY  01-DEC-07 - SATURDAY

Kiran.

Report message to a moderator