CODEWITHSUNDEEP
sqloracle-database
Keshav Raj
Keshav Raj

Reputation: 9

input month and year and get output that whole month date

I have tried this code but I want to pass month and year parameter by using where condition.

WITH d AS
  (SELECT TRUNC ( to_date('01.2012','mm.yyyy'), 'MM' ) - 1 AS dt
  FROM dual
  )
SELECT dt + LEVEL  AS date_month

   FROM d 
  CONNECT BY LEVEL <= ADD_MONTHS (dt, 1) - dt

Upvotes: 1

Views: 226

Answers (2)

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65228

You're very close to the solution, can do such as

SQL> var mon number;
SQL> var year number;
SQL> begin :mon :=&mon; :year := &year; end; -- mon --> 4 and year --> 2020
  2  /

PL/SQL procedure successfully completed
mon
---------
4
year
---------
2020

SQL> 
SQL> WITH d AS
  2  (
  3   SELECT TRUNC( to_date(lpad(:mon,2,'0')||'.'||:year,'mm.yyyy'), 'MM' ) - 1 AS dt
  4     FROM dual
  5  )
  6  SELECT dt + LEVEL AS date_month
  7    FROM d
  8  CONNECT BY LEVEL <= ADD_MONTHS (dt, 1) - dt
  9  /

to list all the dates of April 2020. And replace those parameter values to the desired month and year combinations.

Upvotes: 2

MT0
MT0

Reputation: 167982

You don't need a WHERE clause and you can pass the year and month in as bind variables:

SELECT month_start + LEVEL - 1 AS date_month
FROM   (
  SELECT ADD_MONTHS( TRUNC( TO_DATE( :year, 'YYYY' ), 'YYYY' ), :month - 1 )
           AS month_start
  FROM   DUAL
)
CONNECT BY month_start + LEVEL - 1 < ADD_MONTHS( month_start, 1 )

Upvotes: 2

Related Questions