CODEWITHSUNDEEP
oracle-databaseoracle-sqldeveloperplsqldeveloper
VGK
VGK

Reputation: 1

how get the data starting from 1st month till current date in oracle sql between two dates

select count(EMP_ID) from EMPLOYEE
where created_date between trunc(SYSDATE) - (to number (to_char(SYSDATE,'DD')) - 1)  
and add_months(trunc(SYSDATE) - (to number(to char(SYSDATE,'DD')) - 1), 1) -1

But now I want to rewrite this query in such a way that it will always get data starting from 1st of month till created date. So for example if I run this query now I should get data from 1st January 2018 till 11th January 2018. AND i need to add one more date so that it will fetch from both from and to date .

If I run this query on 1st February then it will return data for entire month of January. I am not sure whether its possible to do in single query.

date format for e.g 22/10/2014

Upvotes: 0

Views: 649

Answers (1)

Littlefoot
Littlefoot

Reputation: 143103

A simple option is

select *
from employee
where to_char(created_date, 'yyyymm') = to_char(sysdate, 'yyyymm')

or any of its derivates, e.g.

where trunc(created_date, 'mm') = trunc(sysdate, 'mm')

However, if there's an index on created_date, it won't be used because of to_char or trunc. In that case, and if it matters (because performance suffers), you'd

where created_date between trunc(sysdate, 'mm') and sysdate

or even

where created_date >= trunc(sysdate, 'mm')

presuming that no row was created in the future.

If you need rows whose created_date is between two parameters, then you'd

where created_date between to_date(:first_date , 'dd/mm/yyyy')
                       and to_date(:second_date, 'dd/mm/yyyy')

presuming that values you entered are in dd/mm/yyyy format.

Upvotes: 1

Related Questions