CODEWITHSUNDEEP
sqloracle-database
HJW
HJW

Reputation: 23443

Oracle get all dates in a month

I am trying to write an SQL statement that would allow me to retrieve all the orders created in the month of September without much luck.

Not working:

select order_number, created_date
from orders
where created_date in to_date('2012-09', 'YYYY-MM');

Working but too long:

select order_number, created_date
from orders
where trunc(created_date) between to_date('2012-09-01', 'YYYY-MM-DD') and to_date('2012-09-30', 'YYYY-MM-DD');

Upvotes: 3

Views: 2307

Answers (4)

Barbaros Özhan
Barbaros Özhan

Reputation: 65408

No need to repeat the created_date column by using between, even the query goes on exploiting the index on that column ,if it has, by using trunc(to_date('2012-09','yyyy-mm'),'Month') for the first date and last_day(to_date('2012-09','yyyy-mm')) for the last date of the month.

with orders(order_number, created_date) as
(
 select 1, date'2012-08-31' from dual union all
 select 2, date'2012-09-01' from dual union all
 select 3, date'2012-09-02' from dual union all
 select 4, date'2012-09-29' from dual union all   
 select 5, date'2012-09-30' from dual union all   
 select 6, date'2012-10-01' from dual    
),
  param(month) as
(
 select to_date('2012-09','yyyy-mm') from dual    
)    
select order_number, created_date 
  from orders
 cross join param
 where created_date between trunc(month,'Month')
                        and last_day(month);

ORDER_NUMBER    CREATED_DATE
------------    ------------
2               01.09.2012 
3               02.09.2012 
4               29.09.2012 
5               30.09.2012

Demo

Upvotes: 0

Anthony Accioly
Anthony Accioly

Reputation: 22481

I think your version that uses between will have better performance, but you can always try the other way around:

WHERE TO_CHAR(created_date, 'YYYY-MM') = '2012-09';

Another option would be EXTRACT:

WHERE 
  EXTRACT(year FROM created_date) = 2012 
  AND EXTRACT(month FROM created_date) = 9;

Update:

Function-Based Indexes can be used since Oracle 8i to improve the performance of this kind of query: 

CREATE INDEX ORDS_CRTD_DT_YYYY_MM_IDX 
   ON orders (TO_CHAR(created_date, 'YYYY-MM'));

Of course that you should avoid creating indexes for no reason (they will slow down write operations), when there are simpler solutions to your problem - like the one @Rob provided - go with it. Just keep in mind that it is possible to use functions such as TRUNC, TO_CHAR and EXTRACT with a column and still avoid full scans.

Upvotes: 1

Rob Farley
Rob Farley

Reputation: 15849

How about:

select order_number, created_date
from orders
where created_date >= to_date('2012-09-01', 'YYYY-MM-DD') 
and created_date < to_date('2012-10-01', 'YYYY-MM-DD');

You should try to leave created_date untouched to make sure that you can leverage indexes on it nicely.

Upvotes: 5

Agung Novian Lunarto
Agung Novian Lunarto

Reputation: 49

Use the following code.

select order_number, created_date
from orders
where TO_CHAR(created_date, 'YYYY-MM') in '2012-09';

Upvotes: 4

Related Questions