Converting serial numbers into time format

Question

Post reply

Converting serial numbers into time format

kylecm2001

SSC Veteran

Points: 293
More actions
March 29, 2009 at 9:32 pm

#128911

i have a problem in converting serial numbers into time format. i have a table OSCL and i am computing for the working time of a technician using this formula: working time = (u_techOutTime-u_techintime)
= (2130-2000)
since both timein and time out are in number and it is a military time without the following symbol (:). how will i going to format it so that is will displayed this result:
= (22:30-20:00)
= 1:30
please help....

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

arun.sas SSChampion Points: 11831 More actions · Answer 1

Hi,

try this

its in the 24 hrs timing so its easy to convert

declare @starttime char(4),

@endtime char(4),

@starttimeconv char(6),

@endtimeconv char(6)

--must pass in char

select @starttime = '2000',@endtime = '2130'

select @starttimeconv = substring(@starttime,1,2)+'.'+substring(@starttime,3,4)

select @endtimeconv = substring(@endtime,1,2)+'.'+substring(@endtime,3,4)

select @starttimeconv,@endtimeconv

RESULT

20.00 21.30

select( (convert(numeric(6,2),@endtimeconv))-(convert(numeric(6,2),@starttimeconv)))

RESULT

1.30

ARUN SAS

andrewd.smith SSCrazy Eights Points: 8444 More actions · Answer 2

The previous solution from arun.sas won't work properly since you can't treat times stored like this as simple decimal values with respect to addition or subtraction. Also, I understand that the u_techintime and u_techOutTime columns of the OSCL table are numeric (? int) not character strings.

I assume that since you are not recording dates as well as times, that any calculated time differences will not exceed 24 hours. However, is it possible for the u_techintime column value to be just before midnight (e.g. 2355) and the u_techOutTime column value to be sometime in the following day (e.g. 0045)? The following T-SQL allows for u_techintime and u_techOutTime to straddle day boundaries as long as the time difference is less than 24 hours.

This version returns the time difference as a datetime value (time value is relative to 1900-01-01 00:00:00.000, which has the datetime value of 0).

SELECT DATEADD(minute, (u_techOutTime % 100 - u_techintime % 100),

DATEADD(hour, (u_techOutTime / 100 - u_techintime / 100),

CASE WHEN (u_techOutTime < u_techintime) THEN 1 ELSE 0 END)) AS ElapsedTime

FROM (

SELECT 2000 AS u_techintime, 2130 AS u_techOutTime UNION ALL

SELECT 2355 AS u_techintime, 0045 AS u_techOutTime UNION ALL

SELECT 0000 AS u_techintime, 0300 AS u_techOutTime UNION ALL

SELECT 1200 AS u_techintime, 1503 AS u_techOutTime UNION ALL

SELECT 0731 AS u_techintime, 0830 AS u_techOutTime

) OSCL

This variation returns the time difference as a char(5) string in the time format HH:MM.

SELECT CONVERT(char(5),

DATEADD(minute, (u_techOutTime % 100 - u_techintime % 100),

DATEADD(hour, (u_techOutTime / 100 - u_techintime / 100),

CASE WHEN (u_techOutTime < u_techintime) THEN 1 ELSE 0 END)),

108) AS ElapsedTime

FROM (

SELECT 2000 AS u_techintime, 2130 AS u_techOutTime UNION ALL

SELECT 2355 AS u_techintime, 0045 AS u_techOutTime UNION ALL

SELECT 0000 AS u_techintime, 0300 AS u_techOutTime UNION ALL

SELECT 1200 AS u_techintime, 1503 AS u_techOutTime UNION ALL

SELECT 0731 AS u_techintime, 0830 AS u_techOutTime

) OSCL

kylecm2001 SSC Veteran Points: 293 More actions · Answer 3

thanks for the help... i used this one and modify some part and it gives me the answer that i wanted. thanks a lot....

SELECT CONVERT(char(5),

DATEADD(minute, (u_techOutTime % 100 - u_techintime % 100),

DATEADD(hour, (u_techOutTime / 100 - u_techintime / 100),

CASE WHEN (u_techOutTime < u_techintime) THEN 1 ELSE 0 END)),

108)