Hello All I have a table in my pg admin database.There is an employee table in this table.Having the field:- 1)name 2)date_of_birth
Now the scenario is that I want to know the birth day for current date and upcoming 20 days For example if current date is 28-Jan-2013 then
1)from_date=28-Jan-2013
2)to_date=16-feb-2013
I want to select all the records from the table for which the date_of_birth
lies between 28-Jan and 16-feb
Try this:
SELECT *
FROM bdaytable
WHERE bdate >= '2013-01-28'::DATE
AND bdate <= '2013-02-16'::DATE;
You may also try overlaps:
SELECT *
FROM bdaytable
WHERE (bdate, bdate)
OVERLAPS ('2013-01-28'::DATE, '2013-02-16'::DATE);
SELECT *
FROM bdaytable
WHERE Extract(month from bdate) >= Extract(month from '2013-01-28'::DATE)
AND Extract(month from bdate) <= Extract(month from '2013-02-16'::DATE)
AND Extract(day from bdate) >= Extract(day from '2013-01-28'::DATE)
AND Extract(day from bdate) <= Extract(day from '2013-02-16'::DATE);
Incorporating Now() and interval to make the query dynamic with current date:
SELECT *
FROM bdaytable
WHERE Extract(month from bdate) >= Extract(month from Now())
AND Extract(month from bdate) <= Extract(month from Now() + Interval '20 day')
AND Extract(day from bdate) >= Extract(day from Now())
AND Extract(day from bdate) <= Extract(day from Now() + Interval '20 day');
extracts, but should work.
(bdate - date_trunc('year',bdate)) BETWEEN (now() - date_trunc('year',now())) AND (now() - date_trunc('year',now()) + interval '20 days'). The idea - to compare intervals from the start of the year.select *
from employee
where
to_char(date_of_birth, 'MMDD') between
to_char(current_date, 'MMDD') and to_char(current_date + 20, 'MMDD')
I think this should work. Use interval.
SELECT *
FROM Employee
WHERE Date_Of_Birth >= now() AND Date_Of_Birth <= now() + interval '20 day'
If you want to get any birth date between these days (and year doesn't matter), then that would be slightly different.
EDIT
If year doesn't matter, while I'm sure there is a better way, this could work. Basically it's just converting all years to a common year (in this case 2000) -- you could do the same with your input parameter as well.
(Date_Of_Birth + (2000-EXTRACT(YEAR FROM Date_Of_Birth) || ' years')::interval)
Curious what others have used in the past as this is probably not the most efficient.
Good luck.
