Finding No. of Days in a Specific Month

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Old Forums

CLOSED - General SQL Server

Finding No. of Days in a Specific Month

Author

Topic

mfdarvesh
Starting Member

10 Posts

Posted - 2006-06-13 : 05:45:28

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

22864 Posts

Posted - 2006-06-13 : 05:50:52

Why doesnt Datediff work?
Dif you try this?

Select DateDiff(day, @date2, @date1)

Madhivanan

Failing to plan is Planning to fail

mfdarvesh
Starting Member

10 Posts

Posted - 2006-06-13 : 05:57:17

Thanks, Madhivanan but it calculates No. of days between two supplied dates, I want to calculate only "Current Months Days" between any two dates.

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-06-13 : 06:56:41

[code]

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

[/code]

Results:
[code]
ST DT DAYS_THIS_MONTH
---------- ---------- ---------------
2006-06-10 2006-06-27 17
2006-06-10 2006-07-27 20

(2 row(s) affected)
[/code]

CODO ERGO SUM

mfdarvesh
Starting Member

10 Posts

Posted - 2006-06-14 : 01:00:10

Thanks Jones, I am trying to use it

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources