Trying to get the differance between two dates

Question

Post reply

Trying to get the differance between two dates

Dakotah

SSC-Addicted

Points: 480
More actions
May 29, 2009 at 11:54 am

#205372

I need the differance of two date's, to find the retrieval time for SSRS reports.
We are checking to see what are the report retrival time.
I only need the Minute, Second, Millisecond date fields.
The date formats are below.
TimeStart2009-05-07 03:00:12.200
TimeEnd: 2009-05-07 03:00:12.450
I would like to have:
03:00:12.200 or 3.00.000

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 1

Lookup DATEDIFF TSQL function in SQL Server Books Online

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 2

select

Hours = datepart(hh,TimeEnd-TimeStart),

Minutes = datepart(mi,TimeEnd-TimeStart),

Seconds = datepart(ss,TimeEnd-TimeStart),

Milliseconds = datepart(ms,TimeEnd-TimeStart),

ElapsedTime = TimeEnd-TimeStart

from

(-- Test Data

select

TimeStart = convert(datetime,'2009-05-07 03:00:12.200'),

TimeEnd = convert(datetime,'2009-05-07 03:00:12.450')

union all

select

TimeStart = convert(datetime,'2009-05-07 03:44:12.840'),

TimeEnd = convert(datetime,'2009-05-07 05:36:04.327')

) a

Results:

Hours Minutes Seconds Milliseconds ElapsedTime

----------- ----------- ----------- ------------ -----------------------

0 0 0 250 1900-01-01 00:00:00.250

1 51 51 487 1900-01-01 01:51:51.487

Jeff Moden SSC Guru Points: 1004340 More actions · Answer 3

andrewd.smith (5/29/2009)
Lookup DATEDIFF TSQL function in SQL Server Books Online

Heh.... try it, Andrew. 😉 You may be surprised how difficult that recommendation actually is.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ntyr321 SSC Enthusiast Points: 125 More actions · Answer 4

Direct subtraction might work in a lot of cases.

The following information is not a direct answer, but these are a few things to be kept in mind before using DATEDIFF.

However, there are a few things to considered. When you are working with dates, are you working on smalldatetime or datetime. Smalldatetime has a range from Jan 1, 1900 to June 6, 2079. DateTime has range from Jan 1, 1753 to December 31, 9999. This info can be obtained from the msdn reference.

By default, DATEDIFF returns int, and int occupies 4 bytes. So the maximum value of int is 2147483647. Lets take two dateparts that would give the maximum values for DATEDIFF, i.e. milliseconds and seconds.

For milliseconds, lets try this query

SELECT DATEDIFF(dd,DATEADD(ms,-2147483647,GETDATE()),GETDATE())

The result is 25! This means that if you do a datediff for two dates that differ by more than 25 days (considering the datepart is ms), there is going to be a arithmetic overflow "Arithmetic overflow error converting expression to data type int".

For seconds, we can try the following query.

SELECT DATEDIFF(yyyy,DATEADD(s,-2147483647,GETDATE()),GETDATE())

The result is 68! This means that if the two dates differ by more than 68 years (considering datepart is seconds), there is going to be a arithmetic overflow.

Thus, it would be better to consider these scenarios before attempting datediff solutions that would scale.

If your scenario warrants dates that exceeds 68 years, a different solution approach might be needed. Just a thought!!!

Dakotah SSC-Addicted Points: 480 More actions · Answer 5

Thanks for all your help I ended up using a negative in SSRS.

(SELECT CONVERT(VARCHAR(12), TimeStart, 114)) AS timestart,

(SELECT CONVERT(VARCHAR(12), TimeEnd, 114)) AS timeend

And this in SSRS:

=Fields!TimeEnd.Value-Fields!TimeStart.Value

Thank's again for this great resource...

I have alot of SQL Books on my shelf, but this resource is by far the best.

Dakotah

lharrison SSC Rookie Points: 45 More actions · Answer 6

How would you get years, months and days from a date range? I have used Datediff and Datepart. The years and months come out fine but the days can very.

For an example if you had a hire date of 2002-05-23 and then a terminated date of 2011-01-13 how would you get the years, months and days from these two dates?