• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 696
  • Last Modified:

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
0
kmccollum
Asked:
kmccollum
  • 3
  • 2
1 Solution
 
Kent OlsenData Warehouse Architect / DBACommented:
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
0
 
kmccollumAuthor Commented:
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
0
 
Dave FordSoftware Developer / Database AdministratorCommented:
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
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Dave FordSoftware Developer / Database AdministratorCommented:
Actually, on second thought, you have add one more test:

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

Open in new window


HTH,
DaveSlash
0
 
kmccollumAuthor Commented:
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.
0
 
Dave FordSoftware Developer / Database AdministratorCommented:
> but not the date ranges that cover may

My second query would.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now