Calculating the Date Difference dynamically

[rajukotla]

Hi all,

Do me a favour.
I need to calculate the DATEDIFF when we pass the dates dynamiically.

I need query..

[debasisdas]

What is the problem with DATEDIFF function.

Is not it working.

[rajukotla]

Sorry,i didnt explain the requirement properly!!!
DATEDIFF(dd,SIS LS.COLUMN016,SI SLS.COLUMN005)/365.

it will get the output.but for a leap year it is not showing properly

[debasisdas]

Sorry,i didnt explain the requirement properly!!!
DATEDIFF(dd,SIS LS.COLUMN016,SI SLS.COLUMN005)/365.

it will get the output.but for a leap year it is not showing properly

Instead of hard coding 365 ,you need to find out the number of days in the year and pass the same to the query dynamically.

[deepuv04]

Instead of hard coding 365 ,you need to find out the number of days in the year and pass the same to the query dynamically.

use it as

DATEDIFF(daySIS LS.COLUMN016,SI SLS.COLUMN005) >= number of days

or

DATEDIFF(daySIS LS.COLUMN016,SI SLS.COLUMN005) <= number of days

or

DATEDIFF(daySIS LS.COLUMN016,SI SLS.COLUMN005) = number of days

[ck9663]

if the two dates are of different year, which year would you follow? say one of the date is last year (not leap year) and the other date is this year (leap year), what's the number of days that you will use as divisor?

-- ck

[rajukotla]

if the two dates are of different year, which year would you follow? say one of the date is last year (not leap year) and the other date is this year (leap year), what's the number of days that you will use as divisor?

-- ck

I WIL FOLLOW THE YEAR OF LEAST YEAR

[ck9663]

I WIL FOLLOW THE YEAR OF LEAST YEAR

assuming SISLS.COLUMN016 is the earlier date between the two.try:

DATEDIFF(dd,SISLS.COLUMN016,SISLS.COLUMN005)/(datediff(dd,DATEADD(qq, DATEDIFF(qq,0,SISLS.COLUMN016), 0) 
, dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,SISLS.COLUMN016)+1, 0))))

or maybe someone already have a function to get the number of days in a year...

-- ck

[debasisdas]

TRY TO USE THIS

[code=sql]
CREATE FUNCTION [dbo].[ufn_GetDaysInYe ar] ( @pDate DATETIME )
RETURNS INT
AS
BEGIN

DECLARE @IsLeapYear BIT

SET @IsLeapYear = 0
IF (YEAR( @pDate ) % 4 = 0 AND YEAR( @pDate ) % 100 != 0) OR
YEAR( @pDate ) % 400 = 0
SET @IsLeapYear = 1

RETURN 365 + @IsLeapYear

END
GO
[/code]

[rajukotla]

How to find out the number of days in a financial year.

suppose from date is sep1st,2006.and to date is aug24th,2009.

the entire range involves financial years.i.e.,

april1st,2006-march31st,2007
april1st,2007-march31st,2008
april1st,2008-march31st,2009
april1st,2009-march31st,2010.
we have to calculate the no.of days of last financial year..
waiting for reply..