Home » SQL & PL/SQL » SQL & PL/SQL » Beginning Date & End Date of the Month
Beginning Date & End Date of the Month [message #260432] Sun, 19 August 2007 20:22 Go to next message
kumarvk
Messages: 211
Registered: July 2004
Senior Member
Dear Experts,

I have the following script for which I change the date every beginning of the month. I just want to know is there any command available like last_day to have for the beginning of the month.

SELECT a.rdc "RDATE",a.lnc "LIC",
a.cno "NC NO",a.chass "CNO",a.ENGNO "ENG NO",
FROM CCR a
WHERE a.rdc BETWEEN
TO_DATE('01-AUG-07 00:00:00', 'dd-mon-yy hh24:mi:ss')
AND TO_DATE('31-AUG-07 23:59:59', 'dd-mon-yy hh24:mi:ss')
ORDER BY a.rdc;

So that every month I need not change the month name, so that the script can run automatically without changing.
Re: Beginning Date & End Date of the Month [message #260433 is a reply to message #260432] Sun, 19 August 2007 20:27 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
> I just want to know is there any command available like last_day to have for the beginning of the month.

Where I come from the beginning of the month is always day "01".
How is it different where you are at?
Re: Beginning Date & End Date of the Month [message #260447 is a reply to message #260432] Sun, 19 August 2007 23:03 Go to previous messageGo to next message
muzahid
Messages: 281
Registered: September 2004
Location: Dhaka, Bangladesh
Senior Member
U may try

select
to_char(to_date('01-'||to_char(last_day(sysdate),'mon-yy')||' 00:00:00','dd-mm-yy hh24:mi:ss'),'dd-mm-yy hh24:mi:ss') str_day,
last_day(sysdate) end_day from dual
Re: Beginning Date & End Date of the Month [message #260456 is a reply to message #260432] Mon, 20 August 2007 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select sysdate, trunc(sysdate,'MONTH') from dual;
SYSDATE             TRUNC(SYSDATE,'MONT
------------------- -------------------
20/08/2007 07:51:28 01/08/2007 00:00:00

1 row selected.

Regards
Michel
Re: Beginning Date & End Date of the Month [message #260473 is a reply to message #260456] Mon, 20 August 2007 01:29 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I'd probably use TRUNC(<a_date>, 'MM') to get the first day of the month.

MHE
Previous Topic: plsql packages
Next Topic: ORA-22275
Goto Forum:
  


Current Time: Thu Dec 08 18:37:02 CST 2016

Total time taken to generate the page: 0.16161 seconds