Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
Solved

# Employee DoB details between two given dates

Posted on 2006-05-24
Medium Priority
539 Views
query requied on  Employee dob details between to given dates, but year not requied. like ( 01 -05) between (31-05)
0
Question by:Vasanth_Kumar

LVL 14

Accepted Solution

GGuzdziol earned 2000 total points
ID: 16749887
I understand that You want rows with value of date column (BOD) in specified range, but You don't want to check year - only day and month.
If so - maybe this is something You need: (I assumed desired range 01-05 to 31-05)

SELECT *
FROM employee
WHERE EXTRACT(MONTH FROM dob) BETWEEN 5 AND 5
AND EXTRACT(DAY FROM dob) BETWEEN 1 AND 31;

or

SELECT *
FROM employee
WHERE TO_DATE(TO_CHAR(dob, 'dd-mm'), 'dd-mm') BETWEEN TO_DATE('01-05', 'dd-mm') AND TO_DATE('31-05', 'dd-mm');

Below is example

SQL> create table employee(id number, dob date);

Table created.

SQL> insert into employee values (1, to_date('02/05/2006', 'DD/MM/YYYY'));

1 row created.

SQL> insert into employee values (2, to_date('02/05/2005', 'DD/MM/YYYY'));

1 row created.

SQL> insert into employee values (3, to_date('02/06/2006', 'DD/MM/YYYY'));

1 row created.

SQL> select * from employee;

ID DOB
---------- ---------
1 02-MAY-06
2 02-MAY-05
3 02-JUN-06

SQL> SELECT * FROM employee WHERE EXTRACT(MONTH FROM dob) BETWEEN 5 AND 5 AND EXTRACT(DAY FROM dob) BETWEEN 1 AND 31;

ID DOB
---------- ---------
1 02-MAY-06
2 02-MAY-05

SQL> SELECT * FROM employee WHERE TO_DATE(TO_CHAR(dob, 'dd-mm'), 'dd-mm') BETWEEN TO_DATE('01-05', 'dd-mm') AND TO_DATE('31-05', 'dd-mm');

ID DOB
---------- ---------
1 02-MAY-06
2 02-MAY-05
0

LVL 3

Expert Comment

ID: 16755936
SELECT *  FROM employee
WHERE dob BETWEEN TO_NUMBER(TO_CHAR(:start_date, 'DDD')) AND TO_NUMBER(TO_CHAR(:end_date, 'DDD'));

'DDD' is format mask for 'Day of Year (1-366)'.
TO_CHAR(:start_date, 'DDD') returns 'Day of Year' as a string, so '2'>'111'. Therefore you have to convert TO_NUMBER.

I don't know how to handle Feb 29th, e.g. TO_NUMBER(TO_CHAR('29-FEB-04', 'DDD')) = 60, but also TO_NUMBER(TO_CHAR('01-MAR-05', 'DDD')) = 60. It's not a problem if both dates are the same year.

JacekMycha
0

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
###### Suggested Courses
Course of the Month11 days, left to enroll