(datediff(ss,,) / 60) rounding down the minute

[asurratt]

Hi,

I have (2) CMD statements that are used to log when a table starts loading in the database with our automated BCP process and another that will log when it completes and determine the duration between.

But...I'm having problems using datediff() to convert the duration calculated in seconds to minutes.

Here's the code snippet used to create the 1st log record:

update TABLE_LOAD_TRACKING
set last_load_start=getdate(),
last_load_end = null,
load_duration = null,
num_rec= 0,
last_updated_by='LOADING'
where table_nm= 'TABLE_A'

This creates a log record like:

table_nm: TABLE_A
last_load_start: 9:59:40 AM
last_load_end: null
load_duration: null
num_rec: 0
last_updated_by: LOADING

Then...when the load is complete another CMD file updates that record to log the endtime and duration.

Here's the code snippet:

update TABLE_LOAD_TRACKING
set last_load_end=getdate(),
load_duration = datediff(ss, last_load_start, getdate()) / 60 ,
num_rec=(select count(*) from TABLE_A),
last_updated_by='COMPLETE'
where table_nm= 'TABLE_A'

...which results in the record now saying:

table_nm: TABLE_A
last_load_start: 9:59:40 AM
last_load_end: 10:01:14 AM
load_duration: 1.0
num_rec: 267916
last_updated_by: COMPLETE

Problem: load_duration should show something like 1.57

My timestamp examples may be a little off, but it's boiling down to the datediff() only setting the # of full minutes that have passed, not the actual x.y number of minutes that have passed.

I've tried playing around with the format of the load_duration field, but with no luck so far. My current setting for that field is: decimal, precision 18, scale 2

Any suggestions or insight are appreciated!!

Thanks!

--Andrea S.
SQL Server 2000

[asurratt]

Hi,

Just to clarify...

I meant to say that the calculation of:
datediff() / 60
is not returning the result as I'd expect of x.y....it's returning in x.0

Thanks!

[asurratt]

Hi again,

Ok, I was able to resolve the problem. Here's the solution for anyone else who may come across this...



My original snippet from the update statement to convert seconds to minutes was:

load_duration = datediff(ss, last_load_start, getdate()) / 60

Let's say the duration was 2.73 minutes. The above would only return 2.0 minutes.




When I change the statement to:

load_duration = datediff(ss, last_load_start, getdate()) / 60.0

...it now results in 2.73


Kind of strange that the whole purpose of me dividing the seconds by 60 to get minutes still resulted in the # of full minutes. It looks like SQL Server needs to be told to return the partial also.

HTH someone in the future.

Take care,

--Andrea S.

[Steve Perry]

Thanks for posting your solution

[ck9663]

This has something to do with data type precedence and the how the division operator works.

If the dividend and the divisor are of different data type, the whole operations returns the data type of the argument with the highest precedence.

If both dividend and divisor are integers, the result is integer with the fractional part truncated.

Happy Coding!!!

~~ CK

[NeoPa]

Andrea:
Kind of strange that the whole purpose of me dividing the seconds by 60 to get minutes still resulted in the # of full minutes. It looks like SQL Server needs to be told to return the partial also.

The more usual approach is for people using SQL Server to learn how it works, rather than to expect it to try to read their minds. It's a very powerful tool if used correctly, but it was never designed to stretch quite that far.