asked on

Find month in date range

I need to build a SQL query that will return all entries where a specified month is in a date range on the record.

I have a to and from date on the record and want to find all that cover May 2012 for example.

Thanks

Kent Olsen

Hi kmccollum,

I'm not really sure what you asking, but date manipulation is very easy in DB2.

SELECT * FROM mytable WHERE year(somedate)=2012 AND month(somedate)=5;

The query above will get all rows where somedate is in May of 2012. Will that work for you?

Kent

kmccollum

ASKER

Kent,

Not really what I have is a date range and I want to find if May 2012 is in that range,

Example...
Start Date = 01/21/2001
End Date = 01/21/2013
This would return because may 2012 is in the date range

however
start date = 01/21/2001
end date = 01/21/2011
this would not return because may 2012 is past the end date

I am trying to see if 05/2012 is between (Sdate) and (Edate)

thanks,
Kevin

Member_2_2484401

Assuming the fromDate is always less than or equal to the toDate, then determining if those dates cover May 2012 is essentially this:

select *
  from MyTable
 where (fromDate between '2012-05-01' and '2012-05-01')
    or (toDate between '2012-05-01' and '2012-05-31')

Open in new window

HTH,
DaveSlash

ASKER CERTIFIED SOLUTION

Member_2_2484401

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kmccollum

ASKER

Ok, that would give the start date's in may or the end date's in may but not the date ranges that cover may

example 1 above would not return with this query.

Member_2_2484401

> but not the date ranges that cover may

My second query would.