• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 414
  • Last Modified:

first day and last day of the month

hi
if i have 2  values of
v_year year & 
v_m  month ,
in the form
how to get first & last day of given month & year
0
NiceMan331
Asked:
NiceMan331
  • 4
  • 3
1 Solution
 
johnsoneSenior Oracle DBACommented:
Converting to a date, gives you the first day of the month, and using the LAST_DAY function gives you the last day, so something like this:

select to_date(v_m || v_year, 'mmyyyy') first_day_of_month,
last_day(to_date(v_m || v_year, 'mmyyyy')) last_day_of_month
from dual;
0
 
NiceMan331Author Commented:
strange

i have this funtion

create or replace FUNCTION CURR_m RETURN NUMBER IS  
Y number(2);
BEGIN
    select period_no into Y from period_detail where year= curr_y() and checked = -1;
   RETURN Y;
END;

Open in new window


select curr_m from dual;
it return  1  , correct
but when i use it like this

select to_date(curr_m || curr_y, 'mmyyyy') first_day_of_month,
last_day(to_date(curr_m || curr_y, 'mmyyyy')) last_day_of_month
from dual;

Open in new window


it return
01-12-14   and  31-12-14
instead of :
01-01-14  and 31-01-14
0
 
johnsoneSenior Oracle DBACommented:
That is because your curr_m is 1 and not 01.  The dates you are getting back are:

December, 01 0014 00:00:00+0000 and December, 31 0014 00:00:00+0000

In a way I would expect that, but I would rather see error that the input doesn't match the supplied format.

Assuming that the 2 variables are numbers and that the year should never be below 1000, then this should work:

SELECT To_date(To_char(curr_m, 'fm00') 
               || curr_y, 'mmyyyy'), 
       Last_day(To_date(To_char(curr_m, 'fm00') 
                        || curr_y, 'mmyyyy')) 
FROM   dual; 

Open in new window

0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
NiceMan331Author Commented:
excellent
0
 
NiceMan331Author Commented:
before i accept your answer , could you please explain to me what is the use of  "fm00" ?
just for knowledge
thanx
0
 
NiceMan331Author Commented:
ok
thanx
0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now