convert int time to half hour increments

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

General SQL Server Forums

New to SQL Server Programming

convert int time to half hour increments

Author

Topic

jmersing
Yak Posting Veteran

57 Posts

Posted - 2011-04-19 : 09:00:41

I'm working with a field called TRLtime it is listed as int, it's display looks like 80911 which Im assuming is 8:09:11 AM

I'd like to convert it to half hour increments of the day, example

80911 = 8:30
130501= 13:00
134503= 13:30

So I would have 48 intervals for one day. Thx

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-19 : 09:06:35

what is the rule ? round to the nearest half & hour ?

KH
[spoiler]Time is always against us[/spoiler]

jmersing
Yak Posting Veteran

57 Posts

Posted - 2011-04-19 : 09:20:42

Correct

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-19 : 09:28:06

quote:
Originally posted by jmersing

Correct

then why 80911 = 8:30 ? shouldn't it be 8:00 ?

and

134503 = 13:30 ? shouldn't it be 14:00 ?

KH
[spoiler]Time is always against us[/spoiler]

jmersing
Yak Posting Veteran

57 Posts

Posted - 2011-04-19 : 09:32:25

I'm sorry you are actually correct on both. I've been looking at these numbers too long :)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-19 : 10:00:31

quote:
Originally posted by jmersing

I'm sorry you are actually correct on both. I've been looking at these numbers too long :)

Lol... I know the feeling...


Declare @t table (
	t varchar(10)
)

Insert Into @t Values 
('80911'),
('130501'),
('134503'),
('234503')


Select 
	t,
	case 
		when convert(int,substring(right('0'+t,6),3,2)) < 15 
			then reverse(stuff(reverse(t),1,4,'0000'))
		when convert(int,substring(right('0'+t,6),3,2)) < 45 
			then reverse(stuff(reverse(t),1,4,'0003'))
		else 
			replace(convert(varchar,convert(int,substring(right('0'+t,6),1,2))+1) + '0000','240000','000000')
		end
From @t

Corey

I Has Returned!!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-04-19 : 10:32:54

[code]select
a.*,
NearestHalfHourString =
convert(varchar(5),dateadd(mi,(datediff(mi,0,dateadd(ss,((a.TM/10000)*3600)+(((a.TM/100)%100)*60)+(a.TM%100)+900,0))/30)*30,0),108),
NearestHalfHourTime =
dateadd(mi,(datediff(mi,0,dateadd(ss,((a.TM/10000)*3600)+(((a.TM/100)%100)*60)+(a.TM%100)+900,0))/30)*30,0)
from
(
select TM = 80911 union all
select TM = 130501 union all
select TM = 133000 union all
select TM = 134459 union all
select TM = 134500
) a[/code]Results:
[code]TM NearestHalfHourString NearestHalfHourTime
----------- --------------------- -----------------------
80911 08:00 1900-01-01 08:00:00.000
130501 13:00 1900-01-01 13:00:00.000
133000 13:30 1900-01-01 13:30:00.000
134459 13:30 1900-01-01 13:30:00.000
134500 14:00 1900-01-01 14:00:00.000[/code]

CODO ERGO SUM

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-04-19 : 10:34:16

[code]
select TRLtime,
TRLtime_mins = ((TRLtime / 10000) * 60) + ((TRLtime / 100 % 100)),
TRLtime_rounded = round((((TRLtime / 10000) * 60) + ((TRLtime / 100 % 100))) / 30.0, 0) * 30,
TRLtime_time = dateadd(minute, round((((TRLtime / 10000) * 60) + ((TRLtime / 100 % 100))) / 30.0, 0) * 30, 0)
from
(
select TRLtime = 80911 union all
select TRLtime = 130501 union all
select TRLtime = 134503
) d
[/code]

KH
[spoiler]Time is always against us[/spoiler]

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2011-04-19 : 10:57:44

Once more into the breach...

select
	a.*,
	NearestHalfHourString =
	convert(varchar(5),dateadd(mi,((((a.TM/10000)*60)+((a.TM/100)%100)+15)/30)*30,0),108),
	NearestHalfHourTime =
	dateadd(mi,((((a.TM/10000)*60)+((a.TM/100)%100)+15)/30)*30,0)
from
	(
	select TM =  80911	union all
	select TM = 130501	union all
	select TM = 133000	union all
	select TM = 134459	union all
	select TM = 134500
	) a

Results:

TM          NearestHalfHourString NearestHalfHourTime
----------- --------------------- -----------------------
      80911 08:00                 1900-01-01 08:00:00.000
     130501 13:00                 1900-01-01 13:00:00.000
     133000 13:30                 1900-01-01 13:30:00.000
     134459 13:30                 1900-01-01 13:30:00.000
     134500 14:00                 1900-01-01 14:00:00.000

CODO ERGO SUM

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-19 : 11:26:12

Weeeeeeeeeeeeeeeeeeeeeeee!


select
	a.*,
	NearestHalfHourString =
	convert(varchar,a.TM/10000)+':'+substring('300',2-(1-abs((TM/100%100+15)/30-1)),2),
	NearestHalfHourTime =
	convert(datetime,convert(varchar,a.TM/10000)+':'+substring('300',2-(1-abs((TM/100%100+15)/30-1)),2))
	--+'00'
	
from
	(
	select TM =  80911	union all
	select TM = 130501	union all
	select TM = 133000	union all
	select TM = 134459	union all
	select TM = 134500
	) a

Corey

I Has Returned!!

jmersing
Yak Posting Veteran

57 Posts

Posted - 2011-04-19 : 12:18:03

Wow - thanks for all of the responses, it appears you all agree on the method. I have a few questions though:

Am I actually typing?

from
(
select TM = 80911 union all
select TM = 130501 union all
select TM = 133000 union all

I dont know what all of the time values would be so I dont see how I could use a union to capture them all. Again probably just my ignorance.

I ran the code and it worked but again, I dont know what the TM values are going to be so Im not sure what to do? Thanks

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-04-19 : 12:59:41

Using MVJ's last post:

TM = TRLtime


select
	a.TRLtime,
	NearestHalfHourString =
	convert(varchar(5),dateadd(mi,((((a.TRLtime /10000)*60)+((a.TRLtime /100)%100)+15)/30)*30,0),108),
	NearestHalfHourTime =
	dateadd(mi,((((a.TRLtime /10000)*60)+((a.TRLtime /100)%100)+15)/30)*30,0)
From YourTableName A

Corey

I Has Returned!!

jmersing
Yak Posting Veteran

57 Posts

Posted - 2011-04-19 : 13:45:37

WOW - That is slick - Now to understand it..... Thanks everyone who contributed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources