Return employee's who were hired in first half of the month

Question

How can I return the employee names from table hr.employees who were hired on first half of the month, i.e. with a date of 1st to 15th of that month.

What have you tried? This should be a trivial query using some of Oracle's date functions. — GolezTrol, Jul 04 '13 at 20:32

score 1 · Accepted Answer · answered Jul 04 '13 at 20:35

1

SELECT 
  *
FROM
  Employee e
WHERE
  TO_NUMBER(TO_CHAR(e.DateHired, 'DD')) <= 15

answered Jul 04 '13 at 20:35

GolezTrol

114,394
18
182
210

Ben · Answer 2 · 2013-07-06T11:20:56.927

Technically, the 15th isn't the first half of the month. It might be the 14th.

To get the first half you would have to work out the maximum number of days in the month, then divide by 2, round down or up (I'd always round down) to get the number of days into the month you need to select from.

Then you need to add this number to the beginning of the month and minus one as the month starts on the first.

You also need to restrict to ensure that you're looking at the same month.

select *
  from employee
 where trunc(datehired) <= trunc(sysdate, 'mm') 
                          + floor(to_number(to_char(last_day(sysdate),'DD'))/2)
                          - 1
   and trunc(datehired,'mm') = trunc(sysdate, 'mm')

ummm, wouldn't this return rows where `datehired` is the thirtieth of last month? BETWEEN would make more sense, although I'm not convinced the OP is interested in just the current month. — APC, Jul 05 '13 at 02:20

Return employee's who were hired in first half of the month

2 Answers2