P: 12

Hi all,
Do me a favour.
I need to calculate the DATEDIFF when we pass the dates dynamiically.
I need query..
 
Share this Question
Expert 5K+
P: 8,127

What is the problem with DATEDIFF function.
Is not it working.
 
P: 12

Sorry,i didnt explain the requirement properly!!!
DATEDIFF(dd,SISLS.COLUMN016,SISLS.COLUMN005)/365.
it will get the output.but for a leap year it is not showing properly
  Expert 5K+
P: 8,127

Sorry,i didnt explain the requirement properly!!!
DATEDIFF(dd,SISLS.COLUMN016,SISLS.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.
  Expert 100+
P: 227

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(daySISLS.COLUMN016,SISLS.COLUMN005) >= number of days
or
DATEDIFF(daySISLS.COLUMN016,SISLS.COLUMN005) <= number of days
or
DATEDIFF(daySISLS.COLUMN016,SISLS.COLUMN005) = number of days
  Expert 2.5K+
P: 2,878

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
 
P: 12

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
  Expert 2.5K+
P: 2,878

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
  Expert 5K+
P: 8,127

TRY TO USE THIS 
CREATE FUNCTION [dbo].[ufn_GetDaysInYear] ( @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

 
P: 12

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,2006march31st,2007
april1st,2007march31st,2008
april1st,2008march31st,2009
april1st,2009march31st,2010.
we have to calculate the no.of days of last financial year..
waiting for reply..
    Question stats  viewed: 4222
 replies: 9
 date asked: Jan 31 '08
