CODEWITHSUNDEEP
sqlsql-serversql-server-2008
user1659363
user1659363

Reputation: 1

how to show the split the days of between two months

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

Upvotes: 0

Views: 2070

Answers (4)

bonCodigo
bonCodigo

Reputation: 14361

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.

Upvotes: 4

Kaf
Kaf

Reputation: 33849

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

Upvotes: 1

Marcin Buciora
Marcin Buciora

Reputation: 449

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

Upvotes: 0

Shiva Komuravelly
Shiva Komuravelly

Reputation: 3280

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

Upvotes: 0

Related Questions