CalBob
asked on
Using DATEDIFF
I am using SQL Server 2008 R2. I have two date fields represented in a varchar column like '01252011'. I am using DATEDIFF to calculate the days between the BegDate and EndDate. Since the date value doesn't have '/' I reformatted the values with '/' and then used DATEDIFF in this way:
DATEDIFF(DAY,(SUBSTRING(Be gDate,1,2) +'/'+SUBST RING(BegDa te,3,2)+'/ '+SUBSTRIN G(BegDate, 5,2)),(SUB STRING(End Date,1,2)+ '/'+SUBSTR ING(EndDat e,3,2)+'/' +SUBSTRING (EndDate,5 ,2)))
This works perfectly for dates within the same year. However, if it cross over a year I get a negative value. For example, the values 12282010 and 02162011 would result in -316 instead of 50 like I think it should.
I see that it is (365-49) = 316 or (366-50) = 316, but negative. So, it must have something to do with the year. And the other values work fine -- those where the Beg and End date are in the same year.
DATEDIFF(DAY,(SUBSTRING(Be
This works perfectly for dates within the same year. However, if it cross over a year I get a negative value. For example, the values 12282010 and 02162011 would result in -316 instead of 50 like I think it should.
I see that it is (365-49) = 316 or (366-50) = 316, but negative. So, it must have something to do with the year. And the other values work fine -- those where the Beg and End date are in the same year.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I agree with above. You are only grabbing, the first 2 characters of year, so you are comparing 12/28/20 to 2/16/20; therefore, that is -316 days.
Try this simplified version:
DATEDIFF(DAY, SUBSTRING(@BegDate, 5, 4)+LEFT(@BegDate, 4), SUBSTRING(@EndDate, 5, 4)+LEFT(@EndDate, 4))
DATEDIFF(DAY, SUBSTRING(@BegDate, 5, 4)+LEFT(@BegDate, 4), SUBSTRING(@EndDate, 5, 4)+LEFT(@EndDate, 4))
ASKER
My oversight. You are correct. Thanks.