Best way to make this a time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Best way to make this a time

Author

Topic

Job
Yak Posting Veteran

69 Posts

Posted - 2006-02-08 : 11:50:22

I have a raw data set that contains fields that are supposed to be times. However, the data leaves no gap in the AM PM for example
TimeIN TimeOUT
09:27:17AM 10:00:02AM
09:28:02AM 09:30:36AM
09:46:36AM 09:47:50AM
10:07:44AM 12:04:20PM
10:42:40AM 10:45:21AM

I need to calculate the difference bwteen TimeIN and TimeOUT.
Importing this data into a table, what would the best way to convert the data to correct time format?

Job

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-02-08 : 12:08:07

Something like this perhaps


create table #tmp (
	TimeIN varchar(10),
	TimeOUT varchar(10)
)

insert into #tmp 
select '09:27:17AM', '10:00:02AM'
union select '09:28:02AM', '09:30:36AM'
union select '09:46:36AM', '09:47:50AM'
union select '10:07:44AM', '12:04:20PM'
union select '10:42:40AM', '10:45:21AM'

SELECT 
	convert(varchar,case when substring(TimeIN,9,2) = 'PM' then 
		case when substring(TimeIN,1,2) = '12' then substring(TimeIN,1,8) 
			else dateadd(hh,12,substring(TimeIN,1,8))
		end else substring(TimeIN,1,8) end,114) as TimeIN,
	convert(varchar,case when substring(TimeOUT,9,2) = 'PM' then 
		case when substring(TimeOUT,1,2) = '12' then substring(TimeOUT,1,8) 
			else dateadd(hh,12,substring(TimeOUT,1,8))
		end else substring(TimeOUT,1,8) end,114) as TimeOUT
from #tmp

drop table #tmp

Job
Yak Posting Veteran

69 Posts

Posted - 2006-02-08 : 12:22:17

Perfect. Also, What about a field that contains a time duration only in minutes and seconds? Example;
Duration
21:00
1:18
2:10
0:40
11:16

This is also a field I need to use in calculations. How do you convert so this is minutes not hours:minues..

Thanks

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-02-08 : 12:29:58

Something like this:


case when len(Duration) = 5 then RIGHT('00:' + Duration, 8) else RIGHT('00:0' + Duration, 8) end

Job
Yak Posting Veteran

69 Posts

Posted - 2006-02-08 : 12:53:17

Thanks again RickD.

Though I do have another quesion. When converting the TimeIN and TimeOUT, I have the results such as;

TimeIN, TimeOUT
09:27:17:000, 10:00:02:000

Why the extra zero's at the end?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2006-02-08 : 12:58:29

They are milliseconds, you will need to Substring again if you want to get rid of them..

Job
Yak Posting Veteran

69 Posts

Posted - 2006-02-08 : 13:00:44

Oh, ok. No they are fine. I'm just doing the subtraction of the OUT vs. the IN and the milliseconds will always be 0, so that won't matter.

Thanks for all the help!

Job

Job
Yak Posting Veteran

69 Posts

Posted - 2006-02-08 : 13:58:01

Ok sorry to keep bugging about this.

When I try to look at the difference between the times ie.

(DATEDIFF ( MINUTE , cast(TimeIN as datetime) , cast(TimeOUT as datetime))/ 60.0) As TmeInSession

I get something like;
TimeOUT TimeIN TmeInSession
10:00:02:000 09:27:17:000 0.550000

I'm trying to get the HH:MM:SS format for the TmeInSession.

Thanks,

Job

Job
Yak Posting Veteran

69 Posts

Posted - 2006-02-08 : 14:14:24

I looked a little more and found some more information;

It's long but I believe this works;

convert(varchar(8),Cast(convert(varchar(8),TimeOUT,108) as datetime) - cast(convert(varchar(8),TimeIN,108)as datetime),108)

Is there a better way?

Job
Yak Posting Veteran

69 Posts

Posted - 2006-02-08 : 16:23:57

Ok a new problem with the same topic.

Earlier in the post we converted the Duration table using
case when len(Duration) = 5 then RIGHT('00:' + Duration, 8) else RIGHT('00:0' + Duration, 8) end

Because this field actually is just minues I can get values over 60 such as 0:67:24 and even 00:158:07

So my case statement looks like this to account for 6 digits;

'Duration' =
case
when len(Duration) = 5
then RIGHT('00:' + Duration, 8)
when len(Duration) = 6
then RIGHT('00:' + Duration, 9)
else RIGHT('00:0' + Duration, 8) end

This makes the Duration look like the example above : 00:158:07

First, I realize this is no longer a date because there are too many digits in the MM section, so do I have to convert this on the fly with the case statement? This comes up because I cannot convert these that are >60 to integers for datetime

Cheers,

Job

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-02-08 : 17:54:04

You have changed the description of what you are after so many times in this thread I find it impossible to understand what you want.

Please state what you want in simple terms:
1. What your input is, including the SQL Server datatype and some examples.
2. What your expected output is, including the SQL Server datatype and some examples.

CODO ERGO SUM

Job
Yak Posting Veteran

69 Posts

Posted - 2006-02-08 : 18:20:35

Ok to clarify,

I now have a column that holds a datetime value called Duration. However, not all rows have datetime values within scope for example, many of the rows have values like 00:67:34 or 00:158:15 etc. I cannot do any aggregates of this data as it will give a conversion error.

My question is can I use the above examples and convert to a numeric so I can sum the result and have the result be in the 2.3 (this would be hours and minutes). The only way I've been able to come up with is to parse the field and then put it back together for example;

cast(substring(Duration,Charindex(':',Duration)+1,Charindex(':',Duration,4)-Charindex(':',Duration)-1)as float)/60 + Cast(Substring(Duration,Charindex(':',Duration,4)+1,2) as float)/3600

Let me know if this still does not make sense.

Job

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-02-08 : 18:57:50

You seemed to have missed my request for input and output SQL Server datatypes and examples.

You said it is a datetime value, but it obviously is not a datetime, because this would be impossible in a datetime column: 00:158:15

It is not clear to me what the format does represent. Is it days:hours:minutes? Can the position of the : characters vary? Can you provide some examples?

What is the output format you are looking for? Do you want two columns, one with hours, one with minutes?

I think you would be better off storing this as a datetime column with the time being the elapsed time from 1900-01-01 00:00:00.000. This is the usual convention for storing elapsed time in SQL Server, and it is easy to parse out the days, hours, minutes, seconds, etc. with various SQL Server date functions like DATEDIFF, DATEADD, and DATEPART.



select
	ELAPSED_TIME	= ET,
	ELAPSED_HOURS	= datediff(hour,0,ET),
	ELAPSED_MINUTES = datediff(minute,0,ET),
	ELAPSED_SECONDS = datediff(second,0,ET),
	MINUTE	= datepart(minute,ET),
	SECOND	= datepart(second,ET)
from
(
select ET=dateadd(second,datediff(second,'09:27:17AM','11:00:02PM'),0) 
) a



ELAPSED_TIME             ELAPSED_HOURS ELAPSED_MINUTES ELAPSED_SECONDS MINUTE      SECOND      
------------------------ ------------- --------------- --------------- ----------- ----------- 
1900-01-01 13:32:45.000  13            812             48765           32          45

(1 row(s) affected)

CODO ERGO SUM

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-02-08 : 20:32:29

quote:

Originally posted by RickD

Something like this perhaps


create table #tmp (
	TimeIN varchar(10),
	TimeOUT varchar(10)
)

insert into #tmp 
select '09:27:17AM', '10:00:02AM'
union select '09:28:02AM', '09:30:36AM'
union select '09:46:36AM', '09:47:50AM'
union select '10:07:44AM', '12:04:20PM'
union select '10:42:40AM', '10:45:21AM'

SELECT 
	convert(varchar,case when substring(TimeIN,9,2) = 'PM' then 
		case when substring(TimeIN,1,2) = '12' then substring(TimeIN,1,8) 
			else dateadd(hh,12,substring(TimeIN,1,8))
		end else substring(TimeIN,1,8) end,114) as TimeIN,
	convert(varchar,case when substring(TimeOUT,9,2) = 'PM' then 
		case when substring(TimeOUT,1,2) = '12' then substring(TimeOUT,1,8) 
			else dateadd(hh,12,substring(TimeOUT,1,8))
		end else substring(TimeOUT,1,8) end,114) as TimeOUT
from #tmp

drop table #tmp

This seems like a simpler way to do this:



select
	TimeIN,
	TimeOut,
	Elapsed_Time = TimeOut-TimeIN,
	TimeIN_STR = convert(varchar(8),TimeIN,108),
	TimeOUT_STR = convert(varchar(8),TimeOUT,108),
	ET_STR = convert(varchar(8),TimeOut-TimeIN,108)
from
(
select	TimeIN = convert(datetime,'09:27:17AM'),
	TimeOUT = convert(datetime,'10:00:02AM')
union all 
select '09:28:02AM', '09:30:36AM'
union all
select '09:46:36AM', '09:47:50AM'
union all
select '10:07:44AM', '12:04:20PM'
union all
select '10:42:40AM', '10:45:21AM'
) a

Results:


TimeIN                  TimeOut                 Elapsed_Time            TimeIN_STR TimeOUT_STR ET_STR   
----------------------- ----------------------- ----------------------- ---------- ----------- --------
1900-01-01 09:27:17.000 1900-01-01 10:00:02.000 1900-01-01 00:32:45.000 09:27:17   10:00:02    00:32:45
1900-01-01 09:28:02.000 1900-01-01 09:30:36.000 1900-01-01 00:02:34.000 09:28:02   09:30:36    00:02:34
1900-01-01 09:46:36.000 1900-01-01 09:47:50.000 1900-01-01 00:01:14.000 09:46:36   09:47:50    00:01:14
1900-01-01 10:07:44.000 1900-01-01 12:04:20.000 1900-01-01 01:56:36.000 10:07:44   12:04:20    01:56:36
1900-01-01 10:42:40.000 1900-01-01 10:45:21.000 1900-01-01 00:02:41.000 10:42:40   10:45:21    00:02:41

(5 row(s) affected)

CODO ERGO SUM

Job
Yak Posting Veteran

69 Posts

Posted - 2006-02-09 : 13:31:42

Sorry, I jumped a bit within the same problem but with different fields.

For a moment lets start with this scenario and forget the TimeIN and TimeOUT.

I have a table that has a varchar field called Duration. The system the data comes from records data such as;
Duration
37:15
45:06
67:19
159:00

The field is represented as mm:ss. It never records hh, just mm:ss. So RickD suggested some code which I changed a little to make it look like hh:mm:ss;

'Duration' =
case
when len(Duration) = 5
then RIGHT('00:' + Duration, 8)
when len(Duration) = 6
then RIGHT('00:' + Duration, 9)
else RIGHT('00:0' + Duration, 8) end

The problem is that when the mm > 60, it of course doesn't follow the datetime format. So what I was doing was taking the data which now looks like;

00:37:15
00:45:06
00:67:19
00:159:00

and parsing out each component and doing the math to convert the minues and seconds into thier decimal equivalents. This works, but now that I'm thinking about it, I should do this conversion before using the case statement to convert to the hh:mm:ss format.

The statment I'm using to make the decimal value is;
cast(substring(Duration,Charindex(':',Duration)+1,Charindex(':',Duration,4)-Charindex(':',Duration)-1)as float)/60 + Cast(Substring(Duration,Charindex(':',Duration,4)+1,2) as float)/3600

So my question was what is the best way to handle a field that only records mm:ss where the mm often are >60? Ultimately I'm simply summing the Duration duration field for a totalduration and the format is something like 45.6 (hours).

Hopefully this is a bit more clear.

Thanks,

Job

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-02-09 : 13:53:36

>>So my question was what is the best way to handle a field that only records mm:ss where the mm often are >60? Ultimately I'm simply summing the Duration duration field for a totalduration and the format is something like 45.6 (hours).

Based on just your last post I would convert your mm:ss to seconds using your favorite string functions then display as hours in a decimal format.


select	convert(decimal(9,2), seconds/3600.00)
from	(
	select	(convert(int, substring(duration, 1, charindex(':', duration)-1)) * 60)
		+convert(int, right(duration, charindex(':', reverse(duration))-1)) as seconds
	from	(
		select '37:15' duration union all
		select '45:06' union all
		select '67:19' union all
		select '159:00'
		) a
	) b

EDIT:
output


----------- 
.62
.75
1.12
2.65

Be One with the Optimizer
TG

Job
Yak Posting Veteran

69 Posts

Posted - 2006-02-09 : 17:54:53

TG,

Thanks for the input. I was converting 2 times needlessly.

Cheers,

Job

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources