0

I am trying to recreate a staff members Excel work in SQL to save time and also drive reporting.

In their spreadsheet, they take 2 time values, minus the smallest from the largest to arrive at a difference, convert that time value to a serialised time value:

Data representation

They then sum that serial integer to define performance calculations.

Is there a conversion or similar process in SQL that can return the same/similar serial time value so I can perform equivalent calculations (or has anyone experience with a function that achieves this)?

I have tried the following line in the code (based on the Excel DateTime explanation here) and the value isn't the same result as Excel...

datediff(MINUTE,cf_pick_pack.date_start, cf_pick_pack.date_end) * (convert(float,1.00000000/1440)) as 'duration_serial'

SQL returns 0.00902777^, which is short of the 0.00923611 that Excel returns.

Optimaximal
  • 545
  • 1
  • 5
  • 28
  • If you have the date/times as T-SQL `DATETIME`/`TIME` types, you can use the regular `DATEDIFF` function. If you must deal with the Excel values explicitly, see [this question](https://stackoverflow.com/q/13850605/4137916). – Jeroen Mostert Dec 10 '19 at 16:24
  • I've added some more flavour to what I've tried. The linked thread in question deals with transforming the data into a SQL datetime. I need to head in the opposite direction. – Optimaximal Dec 10 '19 at 16:39
  • Ahh, wait. I see, Because I was datediff by the minute, SQL was rounding it. – Optimaximal Dec 10 '19 at 16:42

1 Answers1

0

Ok, my bad. SQL was calculating the difference to the nearest minute because the datediff was set to minutes.

The following works...

datediff(SECOND,cf_pick_pack.date_start, cf_pick_pack.date_end) * (convert(float,1.00000000/86400)) as 'duration_serial'
Optimaximal
  • 545
  • 1
  • 5
  • 28