Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Using DATEDIFF

Posted on 2011-02-17
4
Medium Priority
?
849 Views
Last Modified: 2012-05-11
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.

0
Comment
Question by:CalBob
  • 2
4 Comments
 
LVL 29

Accepted Solution

by:
Paul Jackson earned 1000 total points
ID: 34922557
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 34922672
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 34922685
Try this simplified version:
DATEDIFF(DAY, SUBSTRING(@BegDate, 5, 4)+LEFT(@BegDate, 4), SUBSTRING(@EndDate, 5, 4)+LEFT(@EndDate, 4))
0
 

Author Closing Comment

by:CalBob
ID: 34922957
My oversight.  You are correct.  Thanks.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question