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.

Who is Participating?

Software EngineerCommented:
Ensure you are using 4 digit years, change to :
DATEDIFF(DAY,(SUBSTRING(BegDate,1,2)+'/'+SUBSTRING(BegDate,3,2)+'/'+SUBSTRING(BegDate,5,4)),(SUBSTRING(EndDate,1,2)+'/'+SUBSTRING(EndDate,3,2)+'/'+SUBSTRING(EndDate,5,4)))

0

Chief Technology OfficerCommented:
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.
0

Chief Technology OfficerCommented:
Try this simplified version:
DATEDIFF(DAY, SUBSTRING(@BegDate, 5, 4)+LEFT(@BegDate, 4), SUBSTRING(@EndDate, 5, 4)+LEFT(@EndDate, 4))
0

Author Commented:
My oversight.  You are correct.  Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.