convert to 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 2005 Forums

Transact-SQL (2005)

convert to time?

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-04-05 : 05:31:32

i have column 160215 it is mean a time '16:02:15'

TIME
ColTime ColDate
160215 2009-03-15 00:00:00.000
80405 2009-03-15 00:00:00.000

i want that will be

ColDate(DateTime)
2009-03-15 16:02:15.000
2009-03-15 08:04:05.000

thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-06 : 05:44:04

Another method which result's to OP expected result

declare @time char(6)
set @time='160215'
select Stuff(stuff(@time,3,0,':'),6,0,':')

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-06 : 06:04:59

Madhi, try the other value...


declare @time char(6)
set @time='80405'
select Stuff(stuff(@time,3,0,':'),6,0,':')

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-06 : 06:05:58

declare @time int
set @time = 80405
select Stuff(stuff(right('000000' + cast(@time as varchar(6)), 6),3,0,':'),6,0,':')

E 12°55'05.63"
N 56°04'39.26"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-06 : 06:12:19

quote:
Originally posted by Peso

declare @time int
set @time = 80405
select Stuff(stuff(right('000000' + cast(@time as varchar(6)), 6),3,0,':'),6,0,':')

E 12°55'05.63"
N 56°04'39.26"

Yes. Thanks

Madhivanan

Failing to plan is Planning to fail

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2009-04-06 : 06:16:49

another sol.
select stuff( stuff(replicate(0,6-len(@time)) + @time ,3,0,':') ,6,0,':')

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-04-06 : 06:32:24

quote:
Originally posted by ddramireddy

another sol.
select stuff( stuff(replicate(0,6-len(@time)) + @time ,3,0,':') ,6,0,':')

You need cast anyway

select stuff( stuff(replicate(0,6-len(@time)) + cast(@time as varchar(6)),3,0,':') ,6,0,':')

Madhivanan

Failing to plan is Planning to fail

ddramireddy
Yak Posting Veteran

81 Posts

Posted - 2009-04-06 : 06:39:49

Yes. It depends on the datatype of that column.
if it is char type, then no cast is required.
if it is int type, cast is required.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-06 : 06:50:02

STR and REPLACE together with STUFF and STUFF will work for INT, varchar and char source column datatype

DECLARE	 @Time TABLE
	(
		colTimeINT INT,
		colTimeVC VARCHAR(6),
		colTimeC CHAR(6),
		colDate DATETIME
	)

INSERT	@Time
SELECT	160215, '160215', '160215', '2009-03-15' UNION ALL
SELECT	 80405, '80405', '80405', '2009-03-15'

SELECT	*,
	STUFF(STUFF(REPLACE(STR(colTimeINT, 6, 0), ' ', '0'), 3, 0, ':'), 6, 0, ':') AS [INT],
	STUFF(STUFF(REPLACE(STR(colTimeINT, 6, 0), ' ', '0'), 3, 0, ':'), 6, 0, ':') AS [VC],
	STUFF(STUFF(REPLACE(STR(colTimeINT, 6, 0), ' ', '0'), 3, 0, ':'), 6, 0, ':') AS [C]
FROM	@Time

E 12°55'05.63"
N 56°04'39.26"

inbs
Aged Yak Warrior

860 Posts

Posted - 2009-04-07 : 01:05:51

ayamas

i get this types from the source. and i nedd to diff between the column include the hours.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources