General discussion

Locked

Subtract 2 Time fields Formula

By rheal.dugas ·
I have 3 fileds in my databaseMsSql.
Arrive
Depart
Total
I would like to have the total field contain the Value of 'Depart-Arrive'
11:40:44 AM - 1:49:44 PM = 2.9
Could some show me how to create a formula to to this with in the table itself.

This conversation is currently closed to new comments.

14 total posts (Page 1 of 2)   01 | 02   Next
| Thread display: Collapse - | Expand +

All Comments

Collapse -

by NZ_Justice In reply to Subtract 2 Time fields Fo ...

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.

Collapse -

by rheal.dugas In reply to

Poster rated this answer.

Collapse -

by Tony Hopkinson In reply to Subtract 2 Time fields Fo ...

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.

Collapse -

by rheal.dugas In reply to

Poster rated this answer.

Collapse -

by rheal.dugas In reply to Subtract 2 Time fields Fo ...

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.

Collapse -

by derek In reply to Subtract 2 Time fields Fo ...

Use the following to update the table:

update <tablename>
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.

Collapse -

by rheal.dugas In reply to

Poster rated this answer.

Collapse -

by mark.hodgson In reply to Subtract 2 Time fields Fo ...

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] 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

Collapse -

by KeyJammer In reply to Subtract 2 Time fields Fo ...

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

Collapse -

by rheal.dugas In reply to

Poster rated this answer.

Back to Web Development Forum
14 total posts (Page 1 of 2)   01 | 02   Next

Related Discussions

Related Forums