Home » SQL & PL/SQL » SQL & PL/SQL » get first n last day
get first n last day [message #253114] Sun, 22 July 2007 14:38 Go to next message
safwanmehmood
Messages: 14
Registered: August 2005
Junior Member
hi,

user input is 'NOV-07'. i want to get the first and last date of NOV-07.

result should be for first day 01-NOV-07.
for last day 30-NOV-07.

thanks
Re: get first n last day [message #253117 is a reply to message #253114] Sun, 22 July 2007 14:43 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
1) the first day of EVERY month is always 01
2) for the last day - add 1 month to the 1st & then subtract 1 day
Re: get first n last day [message #253119 is a reply to message #253114] Sun, 22 July 2007 14:55 Go to previous messageGo to next message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
2) or use the LAST_DAY function.

Regards
Michel
Re: get first n last day [message #253120 is a reply to message #253114] Sun, 22 July 2007 15:00 Go to previous messageGo to next message
Volder
Messages: 38
Registered: April 2007
Location: Russia
Member
SQL> var dt varchar2;
SQL> exec :dt:='NOV-07';

PL/SQL procedure successfully completed
dt
---------
NOV-07

SQL> select to_date(:dt,'MON-YY') first_day, last_day(to_date(:dt,'MON-YY')) last_day from dual;

FIRST_DAY   LAST_DAY
----------- -----------
01.11.2007  30.11.2007
dt
---------
NOV-07

SQL> 
Re: get first n last day [message #253332 is a reply to message #253120] Mon, 23 July 2007 09:44 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Hi Volder,

Sorry for i am asking this.Because i am getting this error.

----------------------------------------------------------------
SQL> var dt varchar2;
SQL> exec :dt:='NOV-07';
BEGIN :dt:='NOV-07'; END;

*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 1


SQL>

-------------------------------------------------------------------------------

Reds
Thangam.
Re: get first n last day [message #253333 is a reply to message #253332] Mon, 23 July 2007 10:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Default length is 1.
You have to define a length to your varchar2 variable.

Regards
Michel
Re: get first n last day [message #253351 is a reply to message #253114] Mon, 23 July 2007 10:30 Go to previous messageGo to next message
iamdurai
Messages: 96
Registered: April 2007
Location: Chennai
Member

Thanks Michel,

It is working fine now.


Hi Volder

Small request please change the " :dt " input value because at present thats not a valid month('NOV-07').

The above query giving error. please test it and then post due to invalid month (:dt) value.


Regards
Thangam.
Re: get first n last day [message #253366 is a reply to message #253351] Mon, 23 July 2007 10:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Month (MON or MONTH format) depends on your NLS_DATE_LANGUAGE (or NLS_LANG) value, and of course NLS_CALENDAR.
What is yours? Do they include 'NOV'?

Regards
Michel
Re: get first n last day [message #253606 is a reply to message #253366] Tue, 24 July 2007 07:00 Go to previous messageGo to next message
tarunj
Messages: 23
Registered: April 2007
Location: Noida
Junior Member
select to_date('&dt','MON-YY') first_day, last_day(to_date('&dt','MON-YY')) last_day from dual;
Re: get first n last day [message #253626 is a reply to message #253606] Tue, 24 July 2007 07:36 Go to previous message
Michel Cadot
Messages: 64108
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Definitively NO.
Volder version is the correct one (assuming the NLS parameters are correct).
Don't use define variables, use bind variables when you can.

Regards
Michel
Previous Topic: VSIZE()
Next Topic: Re-write the Query
Goto Forum:
  


Current Time: Mon Dec 05 05:06:19 CST 2016

Total time taken to generate the page: 0.23385 seconds