General discussion
-
CreatorTopic
-
January 20, 2006 at 8:37 am #2193778
Subtract 2 Time fields Formula
Lockedby rheal.dugas · about 18 years, 3 months ago
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.Topic is locked -
CreatorTopic
All Comments
-
AuthorReplies
-
-
January 20, 2006 at 3:39 pm #3099778
Reply To: Subtract 2 Time fields Formula
by nz_justice · about 18 years, 3 months ago
In reply to 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.
-
January 23, 2006 at 1:19 pm #3259823
Reply To: Subtract 2 Time fields Formula
by rheal.dugas · about 18 years, 2 months ago
In reply to Reply To: Subtract 2 Time fields Formula
Poster rated this answer.
-
-
January 21, 2006 at 3:58 pm #3098755
Reply To: Subtract 2 Time fields Formula
by tony hopkinson · about 18 years, 3 months ago
In reply to Subtract 2 Time fields Formula
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.-
May 24, 2006 at 5:01 am #3146142
Reply To: Subtract 2 Time fields Formula
by rheal.dugas · about 17 years, 11 months ago
In reply to Reply To: Subtract 2 Time fields Formula
Poster rated this answer.
-
-
January 23, 2006 at 1:21 pm #3259821
Reply To: Subtract 2 Time fields Formula
by rheal.dugas · about 18 years, 2 months ago
In reply to Subtract 2 Time fields Formula
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. -
January 24, 2006 at 1:51 am #3258507
Reply To: Subtract 2 Time fields Formula
by derek · about 18 years, 2 months ago
In reply to Subtract 2 Time fields Formula
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.
-
May 24, 2006 at 5:01 am #3146141
Reply To: Subtract 2 Time fields Formula
by rheal.dugas · about 17 years, 11 months ago
In reply to Reply To: Subtract 2 Time fields Formula
Poster rated this answer.
-
-
January 24, 2006 at 9:39 am #3259189
Reply To: Subtract 2 Time fields Formula
by mark.hodgson · about 18 years, 2 months ago
In reply to Subtract 2 Time fields Formula
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 tableCREATE TRIGGER CalcTotal ON TblTimes
AFTER INSERT ASUPDATE TblTimes
SET total = datediff(s,inserted.ARRIVE,inserted.DEPART)
FROM inserted
WHERE tbltimes.id = inserted.id -
January 24, 2006 at 3:14 pm #3257957
Reply To: Subtract 2 Time fields Formula
by keyjammer · about 18 years, 2 months ago
In reply to Subtract 2 Time fields Formula
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-
May 24, 2006 at 5:01 am #3146139
Reply To: Subtract 2 Time fields Formula
by rheal.dugas · about 17 years, 11 months ago
In reply to Reply To: Subtract 2 Time fields Formula
Poster rated this answer.
-
-
January 27, 2006 at 10:09 pm #3110129
Reply To: Subtract 2 Time fields Formula
by ajit.halder · about 18 years, 2 months ago
In reply to Subtract 2 Time fields Formula
Try this formula,
select (datediff(“mi”,Depart,Arrive))/60.00 diff from
-
May 24, 2006 at 5:01 am #3146140
Reply To: Subtract 2 Time fields Formula
by rheal.dugas · about 17 years, 11 months ago
In reply to Reply To: Subtract 2 Time fields Formula
Poster rated this answer.
-
-
May 24, 2006 at 4:59 am #3146146
Reply To: Subtract 2 Time fields Formula
by rheal.dugas · about 17 years, 11 months ago
In reply to Subtract 2 Time fields Formula
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 -
May 24, 2006 at 5:01 am #3146138
Reply To: Subtract 2 Time fields Formula
by rheal.dugas · about 17 years, 11 months ago
In reply to Subtract 2 Time fields Formula
This question was closed by the author
-
-
AuthorReplies