459,256 Members | 1,700 Online
Need help? Post your question and get tips & solutions from a community of 459,256 IT Pros & Developers. It's quick & easy.

# Calculating the Date Difference dynamically

 P: 12 Hi all, Do me a favour. I need to calculate the DATEDIFF when we pass the dates dynamiically. I need query.. Jan 31 '08 #1
9 Replies

 Expert 5K+ P: 8,127 What is the problem with DATEDIFF function. Is not it working. Jan 31 '08 #2

 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 Jan 31 '08 #3

 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. Jan 31 '08 #4

 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 Jan 31 '08 #5

 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 Jan 31 '08 #6

 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 Feb 1 '08 #7

 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: Expand|Select|Wrap|Line Numbers 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 Feb 1 '08 #8

 Expert 5K+ P: 8,127 TRY TO USE THIS Expand|Select|Wrap|Line Numbers 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   Feb 1 '08 #9

 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,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.. Feb 4 '08 #10