Home » SQL & PL/SQL » SQL & PL/SQL » date formet
date formet [message #202545] Fri, 10 November 2006 05:14 Go to next message
arunprabhu29917
Messages: 15
Registered: September 2006
Location: Tamilnadu
Junior Member

how determine the first and last day for any specific week and month? in oracle query


thanks & regards,
Arunprabhu
Re: date formet [message #202547 is a reply to message #202545] Fri, 10 November 2006 05:20 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Using DATE functions could be one way.

Oracle provides LAST_DAY. For first day, calculate last day (of previous period) and add 1 to it.

[Updated on: Fri, 10 November 2006 05:21]

Report message to a moderator

Re: date formet [message #202555 is a reply to message #202547] Fri, 10 November 2006 06:07 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or you could use TRUNC(date,'WW') and trunc(date,'MM') to get the first day of the week/month.
Re: date formet [message #202629 is a reply to message #202545] Fri, 10 November 2006 10:26 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
And to be complete

First day of week - TRUNC(date,'WW')
Last day of week - TRUNC(date,'WW')+7
First day of month - trunc(date,'MM')
Last day of month - last_day(trunc(date))

If you are going to use them in a between then

First day of week - TRUNC(date,'WW')
Last day of week - TRUNC(date,'WW')+ 7 + (86399/86400)
First day of month - trunc(date,'MM')
Last day of month - last_day(trunc(date)) + + (86399/86400)
Re: date formet [message #202816 is a reply to message #202629] Sun, 12 November 2006 23:37 Go to previous message
arunprabhu29917
Messages: 15
Registered: September 2006
Location: Tamilnadu
Junior Member

Thanks a lot for quick and prompt responce to my query...

Thanks & Regards,
ArunPrabhu
Previous Topic: A question about top n values
Next Topic: Asynchrony call procedure PL/SQL
Goto Forum:
  


Current Time: Sat Dec 10 05:05:30 CST 2016

Total time taken to generate the page: 0.06413 seconds