By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,256 Members | 1,700 Online
Bytes IT Community
+ Ask a Question
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
Share this Question
Share on Google+
9 Replies


debasisdas
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

debasisdas
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

deepuv04
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

ck9663
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

ck9663
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
  1. DATEDIFF(dd,SISLS.COLUMN016,SISLS.COLUMN005)/(datediff(dd,DATEADD(qq, DATEDIFF(qq,0,SISLS.COLUMN016), 0) 
  2. , 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

debasisdas
Expert 5K+
P: 8,127
TRY TO USE THIS

Expand|Select|Wrap|Line Numbers
  1. CREATE FUNCTION [dbo].[ufn_GetDaysInYear] ( @pDate    DATETIME )
  2. RETURNS INT
  3. AS
  4. BEGIN
  5.  
  6.     DECLARE @IsLeapYear        BIT
  7.  
  8.     SET @IsLeapYear = 0
  9.     IF (YEAR( @pDate ) % 4 = 0 AND YEAR( @pDate ) % 100 != 0) OR
  10.         YEAR( @pDate ) % 400 = 0
  11.         SET @IsLeapYear = 1
  12.  
  13.     RETURN 365 + @IsLeapYear
  14.  
  15. END
  16. GO
  17.  
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

Post your reply

Sign in to post your reply or Sign up for a free account.