To find the difference between two datetimes

Question

To find the difference between two datetimes

s.lekha

Right there with Babe

Points: 766
More actions
January 5, 2009 at 3:24 am

#199568

Hi all,
How to we find the difference in hours between two datetimes using report builder?
I have two date columns in dd/mm/yyyy hh:mm:ss format. If i have to find the difference in hours between two dates, how do i accomplish using report builder?
I tried using datediff("HOUR", date1, date2) in the formula field , this gives me error,and is there any way to display the difference in hh:mm:ss format?
Can anyone please help me with this?
Thanks,
s.Lekha

Viewing 12 posts - 1 through 11 (of 11 total)

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

dva2007 SSCertifiable Points: 7707 More actions · Answer 1

If you right click the textbox and see the expression. YOu will find common function and it is having Date and time section. U can find Hour function. YOu can use it like below.

=Hour(Fields!Fieldname.Value)

I m sure u can do rest of your calculation.

regards,

vijay

Ramesh Saive SSC-Insane Points: 24275 More actions · Answer 2

Or you can try using DateDiff("h", date1, date2)...

--Ramesh

s.lekha Right there with Babe Points: 766 More actions · Answer 3

Hi,

Thanks for replying.:)

The expression functionality is available only with report designer, but with report builder its a different case. But i figured out the problem, some links helped me with this, the documentation given for datediff and its interval parameters is wrong, they have actually misguided.

For instance, the datediff function syntax in report builder is datediff(interval, date1, date2). Here the intervals which are available are month, day, hour, minute , second etc. As the documentation says datediff("month",date1,date2) or datediff("MONTH",date1,date2) will render an error, the correct syntax is datediff("Month",date1,date2),so to find the difference in hours we have to give as datediff("Hour",date1,date2) even datediff("hh",date1,date2) will render an error.:crazy:.

So folks looks like report builder is case sensitive, i think this might be of help to anyone whos working with dates in report builder.

But I have another problem here, iam not able to display the same difference value in hh:mm:yyyy format, can anyone guide me pls?

Regards,

lekha

dva2007 SSCertifiable Points: 7707 More actions · Answer 4

Hi,

U can concate the string and display into format like HH:mm:SS

you already have hour from datediff("Hour", date1, date2).

the same way u can get minute and second

Now u can concate the string like below:

Cstr(datediff("Hour", date1, date2)) + ":" +

Cstr(datediff("Minute", date1, date2)) + ":" +

Cstr(datediff("Second", date1, date2))

I havent tested it. But it will give you idea how u can do it.

What kind of problem you are getting? U need to convert the value into string using Cstr so that u can concate.

regards,

vijay

s.lekha Right there with Babe Points: 766 More actions · Answer 5

Hi Vijay,

Thanks for your suggestions.

I think you are talking in terms of report designer, report builder functionality is totally different. The 'cstr' function is not available in builder. Also when the datediff is calculated in hrs, it converts the entire time i.e hh,mm,ss into hours and not only the hour part. Hence to calculate the time in hh,mm,ss separately and concatenating it won't yield the desired result. So iam still wondering how can this be sorted, any help?:(

Thanks,

Lekha

sgmunson SSC Guru Points: 110640 More actions · Answer 6

Calculating the difference in hours, and then expecting to find a value for minutes and seconds isn't realistic, as DATEDIFF doesn't return non-integer values. Calculate the difference in seconds, rather than hours, and use the folliowing logic to guide the rest of the calculations:

Hours = FLOOR(Seconds/3600)

Minutes = FLOOR((Seconds - (Hours * 3600)) / 60)

Seconds = Seconds - (Hours * 3600) - (Minutes * 60)

Look up the FLOOR function in BOL, and you'll see it's the same thing as the old INT function in the BASIC language that came with MS-DOS. You'll have to find the report builder equivalent. It's the largest integer value less than the specified value.

Once you have each of these values, you'll have to assemble them into a string somehow, as there are no time duration formats. You may also have to contend with durations beyond 24 hours, so you may not want to limit yourself to two digits for the hours portion. Something along the lines of the following logic:

RIGHT('00' + Hours,3) + ':' + RIGHT('0' + Minutes,2) + ':' + RIGHT('0' + Seconds,2)

I don't know how you turn numbers into strings in report builder, but that would be necessary, plus, you'll need to know how to concatenate strings ('+' in my logic).

Does that help?

Steve

(aka smunson)

:):):)

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

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

The best way to handle this is to let SQL Server compute the time difference as a datetime (EndTime-StartTime) that is an offset from 1900-01-01 00:00:00.000 and then parse the components of the difference from that.

You can then combine them into strings as you need.

select

[Diff] = ET-ST,

[Days] = datediff(day,0,ET-ST),

[Hours] = datepart(Hour,ET-ST),

[Minutes] = datepart(Minute,ET-ST),

[Seconds] = datepart(Second,ET-ST),

[Milliseconds] = datepart(millisecond,ET-ST)

from

(

select-- Test Data

ST = convert(datetime,'2008/09/22 00:35:33.997'),

ET = convert(datetime,'2009/10/22 01:05:45.443')

) a

Results:

Diff Days Hours Minutes Seconds Milliseconds

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

1901-01-31 00:30:11.447 395 0 30 11 447

(1 row(s) affected)

krishna-335924 Valued Member Points: 59 More actions · Answer 8

For Datediff in report designer use following syntax:

=DateDiff(dateinterval.hour,date1,date2)

wwwnlc111 Old Hand Points: 385 More actions · Answer 9

Hi,

I need to show SQL job execution for last 30 days at what time, this mean I have Date and time.

Now question how I can take Time (hh:mm) for aggregations, it's bit challenging but crazy :w00t:

Thanks.

J Livingston SQL SSC Guru Points: 51550 More actions · Answer 10

wwwnlc111 (5/4/2016)
Hi,
I need to show SQL job execution for last 30 days at what time, this mean I have Date and time.
Now question how I can take Time (hh:mm) for aggregations, it's bit challenging but crazy :w00t:
Thanks.

probably better to post a new question...this thread is a few years old and relates to reporting services.

but if you are specifically asking about SQL Agent execution times....this post may help

https://www.mssqltips.com/sqlservertip/2850/querying-sql-server-agent-job-history-data/

________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day

wwwnlc111 Old Hand Points: 385 More actions · Answer 11

When we query msdb.dbo.sysjobhistory table we are getting run_time what time the job competed,

Now question how can convert run_time as INT to hh:mm in report designer to show HH:MM