SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 Finding No. of Days in a Specific Month
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

mfdarvesh
Starting Member

10 Posts

Posted - 06/13/2006 :  05:45:28  Show Profile
I have two columns i.e. Leavefrom and Leaveto, which tells leave of an employee. It may have any value like 01/02/06 to 13/01/06 or 05/02/06 to 15/04/06 (dd/mm/yy).

I want to calculate No. of Days of Current Month between two dates. lets consider 05/02/06 to 15/04/06, I want to calculate No. of Days of April (which is current month). Is it possible through SQL Query. I search about "DATEDIFF" but it is not working on my problem.

Please help me...

madhivanan
Premature Yak Congratulator

India
22777 Posts

Posted - 06/13/2006 :  05:50:52  Show Profile  Send madhivanan a Yahoo! Message
Why doesnt Datediff work?
Dif you try this?

Select DateDiff(day, @date2, @date1)

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

mfdarvesh
Starting Member

10 Posts

Posted - 06/13/2006 :  05:57:17  Show Profile
Thanks, Madhivanan but it calculates No. of days between two supplied dates, I want to calculate only "Current Months Days" between any two dates.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 06/13/2006 :  06:56:41  Show Profile


select
	ST = convert(varchar(10),ST,121),
	DT = convert(varchar(10),DT,121),
	DAYS_THIS_MONTH =
	case
	-- Compare to last day of month
	when DT > dateadd(mm,datediff(mm,-1,getdate()),-1)
	-- Difference between start date and last day of month
	then datediff(dd,ST,dateadd(mm,datediff(mm,-1,getdate()),-1))
	-- Difference between start date and last date
	else datediff(dd,ST,DT)
	end
from
	(select ST = convert(datetime,'2006-06-10')  )b
	cross join
	(
	Select DT = convert(datetime,'2006-06-27') union all
	Select DT = convert(datetime,'2006-07-27') 
	) a



Results:

ST         DT         DAYS_THIS_MONTH 
---------- ---------- --------------- 
2006-06-10 2006-06-27 17
2006-06-10 2006-07-27 20

(2 row(s) affected)


CODO ERGO SUM
Go to Top of Page

mfdarvesh
Starting Member

10 Posts

Posted - 06/14/2006 :  01:00:10  Show Profile
Thanks Jones, I am trying to use it
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000