CODEWITHSUNDEEP
sqloracle-database
ajm
ajm

Reputation: 13213

How to write a query to get data within a period range?

I have a employee table with columns like 

emp_id, firstname, lastname, month  and year.

Now I need to get employees from table between jan 2012 and march 2012.
In columns month and year we are storing "1" for jan and "3" for march i.e. month numbers and year column has year like 2012.
Please help.

Upvotes: 0

Views: 5293

Answers (4)

Jeevi
Jeevi

Reputation: 3042

Hi this might help you,

 select * from emp
where to_date(concat('1-',concat(concat(month,'-'), year)),'DD-MM-YY') 
      between to_date('1-&month-&year' ,'DD-MM-YY') 
and   to_date('1-&month-&year' ,'DD-MM-YY') ;

i'm converting every month and year value to a data of specified format and comparing them.

Upvotes: -1

Nick
Nick

Reputation: 1128

SELECT * 
FROM  employee
WHERE to_char(year,0000) + to_char(month,00) BETWEEN '201201' AND '201203'

Upvotes: 0

Tim C
Tim C

Reputation: 2940

I think you'd be best off converting everything to datetime before doing the selection. I personally would prefer a single datetime column instead of seperate 'month' and 'year' columns, but if you can't go that way, something like this would work: 

SELECT * FROM employees 
 WHERE CAST( CAST(year AS VARCHAR(4)) +
            RIGHT('0' + CAST(month AS VARCHAR(2)), 2) +
            '00'
            AS DATETIME)
BETWEEN
    CAST( CAST(start_year AS VARCHAR(4)) +
            RIGHT('0' + CAST(start_month AS VARCHAR(2)), 2) +
            '00'
            AS DATETIME)
AND
    CAST( CAST(end_year AS VARCHAR(4)) +
            RIGHT('0' + CAST(end_month AS VARCHAR(2)), 2) +
            '00'
            AS DATETIME)

It's kind of ugly, unfortunately. Maybe it could be cleaned up by converting the start and end dates to datetime before the selection statement and storing them in variables?

Upvotes: 0

juergen d
juergen d

Reputation: 204766

Employees between Januar and March 2012:

select * from employee
where year = 2012 and month between 1 and 3

Employees between Januar 2012 and March 2013:

select * from employee
where (year = 2012 and month between 3 and 12)
or (year = 2013 and month between 1 and 3)

Upvotes: 3

Related Questions