Click here to Skip to main content
14,267,453 members
Rate this:
Please Sign up or sign in to vote.
See more:
I am trying to calculate shipping performance- Im getting stuck in several areas and I hope i can get some help!

I have an Estimated Ship Date, an Appt Date, and an Actual Ship date.

Im measuring warehouse performance, so in most cases shipments leave same day. But in others, they may leave a few days early or late.

The problem that i am having is the correct output. I want to show the Values in DD:HH:MM, but the syntax for DateDiff doesn't give me an accurate day to use:

For Example, a shipment was supposed to leave on 6/3/2019 @ at 8 am, but didnt leave the warehouse until 7/22/2019 @ 6:30 AM. In this case, DateDiff calcs 49 days, when really its 48 days 22 hours and 30 minutes late. Here is an example of some of the syntax i am using:

What I have tried:

create table #test (
  EstimatedShipDate datetime,
  AppointmentShipDate datetime,
  ActualShipDate datetime

insert into #test values ('2019-07-01 11:00', '2019-07-01 11:00','2019-06-30 10:30');
insert into #test values ('2019-07-08 13:45', null,'2019-07-01 22:00');
insert into #test values ('2019-07-09 15:00', null,'2019-07-10 15:00');
insert into #test values ('2019-07-03 15:00', null,'2019-07-04 15:00');
insert into #test values ('2019-07-08 15:00', null,'2019-07-08 15:00');
insert into #test values ('2019-07-08 15:00', null,'2019-07-08 22:00');
insert into #test values ('2019-07-03 08:00', null,'2019-07-04 15:00');
insert into #test values ('2019-07-03 08:00', null,'2019-07-03 06:30');
insert into #test values ('2019-06-03 08:00', null,'2019-07-22 06:30');
--insert into test values (3, '2012-01-02 12:00');


DATEDIFF(DAY,ISNULL(CAST(AppointmentShipDate as DateTime),CAST(EstimatedShipDate as DateTime)), CAST(ActualShipDate as DateTime)) as Days,

convert(varchar, CAST(ActualShipDate as DateTime)-ISNULL(CAST(AppointmentShipDate as DateTime),CAST(EstimatedShipDate as DateTime)),108) as DateSubtract_Convert,

convert(varchar,ISNULL(CAST(AppointmentShipDate as DateTime),CAST(EstimatedShipDate as DateTime)-CAST(ActualShipDate as DateTime) ),108) as DateSubtract_ConvertEarly
from #TEST

Using the example above output looks like:

EstimatedShipDate	2019-06-03 08:00:00.000
AppointmentShipDate	null
ActualShipDate	2019-07-22 06:30:00.000
Days	49	
DateSubtract_Convert	22:30:00
DateSubtract_ConvertEarly	01:30:00

Any advice is greatly appreciated!
Updated 1-Aug-19 8:42am
Afzaal Ahmad Zeeshan 1-Aug-19 13:06pm
Basic arithmetic.
Rate this:
Please Sign up or sign in to vote.

Solution 1

Simple: calculate the total difference in minutes, and use division and the modulus operator[^] to convert that to days, hours, and minutes.
    DateDiff(minute, IsNull(AppointmentShipDate, EstimatedShipDate), ActualShipDate) As TotalMinutes,
    CAST(FLOOR(DateDiff(minute, IsNull(AppointmentShipDate, EstimatedShipDate), ActualShipDate) / 1440) As int) As Days,
    CAST(FLOOR((DateDiff(minute, IsNull(AppointmentShipDate, EstimatedShipDate), ActualShipDate) % 1440) / 60) As int) As Hours,
    CAST(FLOOR(DateDiff(minute, IsNull(AppointmentShipDate, EstimatedShipDate), ActualShipDate) % 60) As int) As Minutes
For your example row, that gives:
EstimatedShipDate    2019-06-03 08:00:00.000
AppointmentShipDate  null
ActualShipDate       2019-07-22 06:30:00.000
TotalMinutes         70470
Days                 48
Hours                22
Minutes              30
NB: DateDiff doesn't quite work as you might expect, as explained in this StackOverflow answer[^]. It counts the number of times you pass the specified boundary.

So, for example, between 2018/12/31 23:59:59 and 2019/01/01 00:00:01, it will return a difference of 1 for minute, hour, day, month and year.
MadMyche 1-Aug-19 14:13pm
Member 14547089 1-Aug-19 14:44pm
Omg thank you!! Im not as savvy with code so i pickup from others--- this is extremely helpful! +5 to you!
Rate this:
Please Sign up or sign in to vote.

Solution 2

Richard's answer is correct; and for ad-hoc queries or in a report this would be the way to go

The first thing you need to understand is that DateTime items are actually numbers, and that any math you are doing with them is a conversion to the number, perform the math on the number, and convert it back to a DateTime object.

SQL servers in general are optimized for the general CRUD functions. When it comes to performing functions, manipulations, math; it is not the best place to do it.

Data sources are just that- sources of data. What you do with that data should be done in the application that is using the data itself; and usually at the Presentation Layer

For example; if you were using C#, you could use a variable defined as a TimeSpan for the intervals you need.
// Two DateTime objects
DateTime EstimatedShipDate = DateTime.Parse("06/03/2019 08:00");
DateTime ActualShipDate = DateTime.Parse("07/22/2019 06:30");

// Difference between the 2 objects
TimeSpan AmountOverDue = ActualShipDate - EstimatedShipDate;

// "Presentation Layer" version of the overdue time
string OverDue = AmountOverDue.ToString(@"dd\:hh\:mm"); 
TimeSpan Struct (System) | Microsoft Docs[^]
Custom TimeSpan format strings - .NET | Microsoft Docs[^]

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100