Solved

Using DATEDIFF

Posted on 2011-02-17
4
833 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 250 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 59

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 59

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

914 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now