Link to home
Start Free TrialLog in
Avatar of CalBob
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(BegDate,1,2)+'/'+SUBSTRING(BegDate,3,2)+'/'+SUBSTRING(BegDate,5,2)),(SUBSTRING(EndDate,1,2)+'/'+SUBSTRING(EndDate,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.

ASKER CERTIFIED SOLUTION
Avatar of Paul Jackson
Paul Jackson
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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))
Avatar of CalBob
CalBob

ASKER

My oversight.  You are correct.  Thanks.