• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 693
  • 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

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