March 1, 2018 at 10:29 pm
Hi,
I have two fields in a table that has time recorded in number format (eg; 9:31 PM is stored as 213100 in the database).
Can anyone please let me know how to extract this field in time format HH:MM::SS and then subtract these fields to find the time difference.
Thanks,
Sriram
March 2, 2018 at 1:20 am
pajjuris - Thursday, March 1, 2018 10:29 PMHi,I have two fields in a table that has time recorded in number format (eg; 9:31 PM is stored as 213100 in the database).
Can anyone please let me know how to extract this field in time format HH:MM::SS and then subtract these fields to find the time difference.Thanks,
Sriram
Assuming that your "number format" fields are integers, the following will convert each one to TIME data typeSELECT CONVERT(VARCHAR(10), DATEADD(HOUR, [YourFieldName]/10000, DATEADD(MINUTE, ([YourFieldName]%10000)/100, DATEADD(SECOND, [YourFieldName]%100, 0))), 108);
March 2, 2018 at 1:35 am
Below are 2 different methods for calculating the time differences
1 - Calculate the INT difference, and then convert the result to TIME.SELECT [Time1] = [YourIntField1]
, [Time2] = [YourIntField2]
, DiffTime = CONVERT(VARCHAR(10), DATEADD(HOUR, ([YourIntField2] - [YourIntField1])/10000, DATEADD(MINUTE, (([YourIntField2] - [YourIntField1])%10000)/100, DATEADD(SECOND, ([YourIntField2] - [YourIntField1])%100, 0))), 108);
FROM [YourTable]
2 - Convert each field to TIME and then calculate the difference.SELECT [Time1] = [YourIntField1]
, [Time2] = [YourIntField2]
, DiffTime = CONVERT(VARCHAR(10), DATEADD(SECOND, DATEDIFF(SECOND, DATEADD(HOUR, [YourIntField1]/10000, DATEADD(MINUTE, ([YourIntField1]%10000)/100, DATEADD(SECOND, [YourIntField1]%100, 0))), DATEADD(HOUR, [YourIntField2]/10000, DATEADD(MINUTE, ([YourIntField2]%10000)/100, DATEADD(SECOND, [YourIntField2]%100, 0)))), 0), 108);
FROM [YourTable]
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply