Solved

# Find month in date range

Posted on 2012-08-22
662 Views
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
Question by:kmccollum

LVL 45

Expert Comment

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

Author Comment

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

LVL 18

Expert Comment

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

HTH,
DaveSlash
0

LVL 18

Accepted Solution

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

HTH,
DaveSlash
0

Author Comment

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

LVL 18

Expert Comment

> but not the date ranges that cover may

My second query would.
0

## Featured Post

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…