Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 543
  • Last Modified:

SQL Number of Days between 2 dates giving results per month

have two columns in a SQL table that contain dates in a date range. I need to count the days in the date range and group by year/month. If the dates in the range are in the same month or in two consecutive months I can calculate the days. If the dates span two or more months, I don't know how to group by the months in between the begin date and end date.

For example, if Begin_Date is 2004-10-01 and End_Date is 2005-01-06, how do I group by each month in the date range so the output looks something like this:

YR_MTH  DAYS
2004/10 31
2004/11 30
2004/12 31
2005/01 6
0
flickimp1717
Asked:
flickimp1717
1 Solution
 
vinurajrCommented:
Try this... You can do much more if u play around the date functions...
declare @table table(ID int identity,DATE_S datetime,DATE_E datetime)
insert into @table 
select getdate()-60,getdate()+60
union all
select Getdate()-60,Getdate()+30
union all 
select Getdate(),Getdate()+1
union all 
select Getdate()+30,Getdate()-30
 
select left(CONVERT(VARCHAR(10),DATE_S,111),7),
right(CONVERT(VARCHAR(10),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATE_S)+1,0)),111),2)Days,
left(CONVERT(VARCHAR(10),DATE_E,111),7),
right(CONVERT(VARCHAR(10),DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,DATE_E)+1,0)),111),2)Days,
DATE_S from @table
GROUP BY DATE_S,DATE_E

Open in new window

0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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