Home » SQL & PL/SQL » SQL & PL/SQL » question on getting first date of a month
question on getting first date of a month [message #197071] Mon, 09 October 2006 20:45 Go to next message
pcgame4u
Messages: 14
Registered: July 2006
Junior Member
Dear all brother,
i am going to extract the first day of a month.
is there any better solution to extract rather than below?
*i dont want to TO_CHAR and TO_DATE again!*

ps: I want to have a Date type result in order to use it in where clause.
 SELECT TO_DATE(TO_CHAR(TRUNC(SYSDATE, 'MM'), 'YYYYMM'),'YYYYMM')
FROM dual;
Re: question on getting first date of a month [message #197072 is a reply to message #197071] Mon, 09 October 2006 21:09 Go to previous messageGo to next message
BlackSwan
Messages: 25045
Registered: January 2009
Location: SoCal
Senior Member
For me the first day of the month is always the 1st (01).
Re: question on getting first date of a month [message #197077 is a reply to message #197071] Mon, 09 October 2006 21:19 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Doesn't the following solve your question:

SQL> select trunc(sysdate,'mm') from dual;

TRUNC(SYS
---------
01-OCT-06
Re: question on getting first date of a month [message #197112 is a reply to message #197077] Tue, 10 October 2006 02:01 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or you could do
SELECT last_day(add_months(sysdate,-1))+1
FROM   dual;

LAST_DAY(
---------
01-OCT-06

It's longer, less obvious, and probably slightly slower then @ebrians approach, but it's another way of doing it.
Previous Topic: explain plan error
Next Topic: Problem r'ing place of inserting
Goto Forum:
  


Current Time: Wed Dec 07 18:51:33 CST 2016

Total time taken to generate the page: 0.08673 seconds