CODEWITHSUNDEEP
oracle-database
Prabha
Prabha

Reputation: 273

Get first date of month in oracle

I want to get the distinct months between start and end date. The selected month date would be the first date of the corresponding month. For example, my start date is 01/30/2015 00:00:00 & end date is 11/30/2015 23:59:59

Query I created as :

with data1 as(
select to_date('01/30/2015 00:00:00','MM/DD/YYYY HH24:MI:SS')+level-1 dt 
  from dual
connect by level <= to_date('11/30/2015 23:59:59','MM/DD/YYYY HH24:MI:SS')-to_date('01/30/2015 00:00:00','MM/DD/YYYY HH24:MI:SS')+1)
select  distinct trunc(dt,'MM') as date1
  from data1
 where dt between to_date('01/30/2015 00:00:00','MM/DD/YYYY HH24:MI:SS') and to_date('11/30/2015 23:59:59','MM/DD/YYYY HH24:MI:SS')
   and trunc(dt,'MM') <= to_date('11/30/2015 23:59:59','MM/DD/YYYY HH24:MI:SS')
 order by trunc(dt,'MM')

Output:

01/01/2015 00:00:00  ,
02/01/2015 00:00:00  ,
03/01/2015 00:00:00  ,
04/01/2015 00:00:00  ,
05/01/2015 00:00:00  ,
06/01/2015 00:00:00  ,
07/01/2015 00:00:00  ,
08/01/2015 00:00:00  ,
09/01/2015 00:00:00  ,
10/01/2015 00:00:00  ,
11/01/2015 00:00:00

This query result in a correct output, but I have a doubt that the above query will run in all versions of oracle database without any issue. Please give me instructions.

Upvotes: 0

Views: 8531

Answers (1)

Shannon Severance
Shannon Severance

Reputation: 18410

Your query will work. But using ADD_MONTHS allows for the same results with much less work in a more direct way.

select trunc(add_months(date '2015-01-30', level - 1), 'MONTH') as THE_MONTH
from dual
connect by trunc(add_months(date '2015-01-30', level - 1), 'MONTH')
    <= date '2015-11-30'
order by THE_MONTH

Upvotes: 1

Related Questions