Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Using DATEDIFF

Posted on 2011-02-17
4
Medium Priority
?
843 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Long way back, we had to take help from third party tools in order to encrypt and decrypt data.  Gradually Microsoft understood the need for this feature and started to implement it by building functionality into SQL Server. Finally, with SQL 2008, …
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

715 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