Subtract 2 Time fields Formula

Not sure if you can do this in mysql, but create a function 'Depart-Arrive'.

Create the function outside the table but with in the database.

Then add this function to your table as a column.

DateDiff(s,Depart,Arrive) will give the number of seconds between the two dates divide by 60 for hours etc..
DateDiff works on boundaries and returns an integer (or at least it did last time I looked) so DateDiff with h(ours) as a parameter would return 2 in your example.

This works I have added the ABS function as it gave me a minus number with the example above. There is one problem though, it rounds off the number and does not give me minutes or part thereof...
Lets say arrive time was 1:50 pm depart time was 2:55 pm
The total should be 1.05 ...Any Idea how I can get to do this and not round off.

Use the following to update the table:

update
set Total = Depart - Arrive

You would also have to add the date portion to the Arrive/Depart fields to make this work correctly if the Arrive/Depart times crossed midnight.

Under database design rules, the table design would be more efficient if you did not store Total as a separate field, as this is derived data. The overheads of calculating this, each time you need to, are very small.

If you must store the difference, consider using a trigger on the table to calculate the Total column.

The following can be used to create a sample table and trigger.

-- Create sample table

CREATE TABLE [dbo].[TblTimes] (
[Id] [numeric](18, 0) IDENTITY (1, 1) NOT NULL ,
[Arrive] [datetime] NULL ,
[Depart] [datetime] NULL ,
[Total] [float] NULL
) ON [PRIMARY]

GO
-- Create trigger on table

CREATE TRIGGER CalcTotal ON TblTimes
AFTER INSERT AS

UPDATE TblTimes
SET total = datediff(s,inserted.ARRIVE,inserted.DEPART)
FROM inserted
WHERE tbltimes.id = inserted.id

Try this, I have just used literals that I converted to DATETIME values to do the date math but you can substitute column names from your table:

SELECT
CAST('2005-01-24 11:40:44AM' AS DATETIME) AS DEPART,
CAST('2005-01-24 1:49:44PM' AS DATETIME) AS ARRIVE,
CAST('Total Arrival Time: ' +
RTRIM(CAST(DATEDIFF(mi,CAST('2005-01-24 11:40:44AM' AS DATETIME),
CAST('2005-01-24 1:49:44PM' AS DATETIME)) /60 AS CHAR(2)))
+ ' hrs ' +
RTRIM(CAST(DATEDIFF(mi,CAST('2005-01-24 11:40:44AM' AS DATETIME),
CAST('2005-01-24 1:49:44PM' AS DATETIME)) %60 AS CHAR(2)))
+ ' mins' AS CHAR(40)) AS TOT_ARRIVAL_TEXT,
CAST(DATEDIFF(mi,CONVERT(DATETIME,'2005-01-24 11:40:44AM'),
CONVERT(DATETIME,'2005-01-24 1:49:44PM')) /60 +
DATEDIFF(mi,CONVERT(DATETIME,'2005-01-24 11:40:44AM'),
CONVERT(DATETIME,'2005-01-24 1:49:44PM')) %60 / 100.00 AS DECIMAL(6,2)) AS TOT_HRS_MINS

Try this formula,

select (datediff("mi",Depart,Arrive))/60.00 diff from

Found a Solution that works. Thank You for all your comments.

CREATE VIEW dbo.TTA
AS
SELECT TOP 100 PERCENT CAST(datecreated AS DATETIME) AS Depart, CAST(accepteddate AS DATETIME) AS Arrive, CAST( RTRIM(CAST(DATEDIFF(mi,
CAST(datecreated AS DATETIME), CAST(accepteddate AS DATETIME)) / 60 AS CHAR(2))) + ' hrs ' + RTRIM(CAST(DATEDIFF(mi,
CAST(datecreated AS DATETIME), CAST(accepteddate AS DATETIME)) % 60 AS CHAR(2))) + ' mins' AS CHAR(40)) AS TOT_ARRIVAL_TEXT,
CAST(DATEDIFF(mi, CONVERT(DATETIME, datecreated), CONVERT(DATETIME, accepteddate)) / 60 + DATEDIFF(mi, CONVERT(DATETIME, datecreated),
CONVERT(DATETIME, accepteddate)) % 60 / 100.00 AS DECIMAL(6, 2)) AS TOT_HRS_MINS, Log_ID AS Expr1
FROM dbo.ONSA_NSS

This question was closed by the author