CODEWITHSUNDEEP
sqlpostgresql
Suri
Suri

Reputation: 518

How to get only date/month part from a date in postgres SQL

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

Upvotes: 20

Views: 53836

Answers (3)

bonCodigo
bonCodigo

Reputation: 14361

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);

with extract, month, day:

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');

Upvotes: 40

Clodoaldo Neto
Clodoaldo Neto

Reputation: 125214

select *
from employee
where 
    to_char(date_of_birth, 'MMDD') between 
    to_char(current_date, 'MMDD') and to_char(current_date + 20, 'MMDD')

Upvotes: 4

sgeddes
sgeddes

Reputation: 62831

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.

Upvotes: 0

Related Questions