how to show the split the days of between two months

Question

I have a table with columns empname,fdate,tdate,totleave

If the employee takes a leave from 28th January to 2nd February, totally 5 days leave, while preparing the salary, I want to retrieve the January leave days only, that 3 days only I want to retrieve, the remaining days of February will be retrieved in the month of February, can you tell me how to write the query for this.

This is my query but it is wrong

select ((month(tdate )) - (day(fdate ))) as no_of_days,month(fdate )as month 
from leavedepot

How about Saturdays & Sundays? Are they counted as well? – Kaf Jan 03 '13 at 11:23 — Kaf, Jan 03 '13 at 11:23

score 4 · Answer 1 · edited May 23 '17 at 11:48

Can you please try the following? It uses DateDiff().

select datediff(day, startdate, enddate) as no_of_days,
month(fdate )as month 
from leavedepot;

EDIT : SQLFIDDLE DEMO

Sample Data:

ID  STARTDATE   ENDDATE
1   2012-01-28  2012-02-02
2   2012-02-03  2012-02-10

Query:

    SELECT month(startdate) as Month_Number,
    datename(month, startdate) as Month, -- month is a reserved word
    case when month(startdate) <> month(enddate)
    then datediff(day, startdate, DATEADD(month, 
               ((YEAR(startdate) - 1900) * 12)
               + MONTH(startdate), -1)) 
    else datediff(day, startdate, enddate)
    end
    as Leaves
    from myleaves
    ;

Results:

MONTH_NUMBER    MONTH       LEAVES
1               January     3
2               February    7

If you would want to exclude weekends by any chance...: Here we use a variable to define start end dates, but you can use your own table columns for it as well. :) Reference post: get DATEDIFF excluding weekends using sql server. You need to incorporate this query into the above.

declare @d1 datetime, @d2 datetime
select @d1 = '9/9/2011',  @d2 = '9/18/2011'

select datediff(dd, @d1, @d2) - (datediff(wk, @d1, @d2) * 2) -
       case when datepart(dw, @d1) = 1 then 1 else 0 end +
      case when datepart(dw, @d2) = 1 then 1 else 0 end;

You may still remove your 2 days to exclude start and enddate...The functions we use datepart.

Please take a look at all three samples here and comment which works best for you + if you need any further clarifications. — bonCodigo, Jan 03 '13 at 11:37
You had not considered what user was asking for in your queries before. Your second query is almost like mine now. — Kaf, Jan 03 '13 at 12:07
@Kaf don't think I copied you, totally different approaches ;) — bonCodigo, Jan 03 '13 at 12:11

score 1 · Answer 2 · answered Jan 03 '13 at 11:30

Without considering Saturday & Sundays (Please replace variables with column names);

declare @fdate datetime = '20130128'
declare @tdate datetime = '20130202'

Select case  
         when datepart(mm,@fdate) = datepart(mm,@tdate) --check if same month
         then datediff(day,@fdate, @tdate) 
         else datediff(day,@fdate, 
            convert(varchar(4),datepart(yy,@fdate)) + 
            right('0' + convert(varchar(2), datepart(mm,@fdate)+1),2) + '01') - 1 
         end
AS leavesOfMonth

score 0 · Answer 3 · answered Jan 03 '13 at 11:18

This gives you last day of the current month so you could achieve it easily

SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))

Similarly you could get the First day of the current month in the next month while you are calculating for february.

Last Day of previous month

SELECT DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0))

score 0 · Answer 4 · answered Jan 03 '13 at 11:27

This code gives you number of days from fdate till end of its month:

select 
datediff(dd,fdate,
dateadd(dd, -1, dateadd(mm,1,cast
(cast(datepart(yy,fdate) as varchar) + '-' + 
cast(datepart(mm,fdate) as varchar) + '-01'
as datetime
))))
from dbo.TST

TST is table of your construction with dates

how to show the split the days of between two months

4 Answers4