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:
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.