Need help to convert value into proper Time

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)

Need help to convert value into proper Time

Author

Topic

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2009-11-21 : 00:41:32

Me have table and data as follow,

tTime
TID | TTime
-----------------
1 | 023000
2 | 200000
3 | 223000
4 | 103000
5 | 153000

1- My TTime format is 6 digit number.
2- 023000 means 230AM
3- 200000 means 800PM
4- 223000 means 1030PM
5- 103000 means 1030AM
6- 153000 means 330PM

I want to make it my tTime show as follow,
TID | TTime
-----------------
1 | 230AM
2 | 800PM
3 | 1030PM
4 | 1030AM
5 | 330PM

Me really need help

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-11-21 : 08:29:17

[code]
select dateadd(hour, TTime / 10000, dateadd(minute, TTime % 10000 / 100, dateadd(second, TTime % 100, 0)))
[/code]

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

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-11-21 : 15:00:02

[code]DECLARE @Sample TABLE
(
TID INT,
TTime CHAR(6)
)

INSERT @Sample
SELECT 1, '023000' UNION ALL
SELECT 2, '200000' UNION ALL
SELECT 3, '223000' UNION ALL
SELECT 4, '103000' UNION ALL
SELECT 5, '153000'

SELECT *,
LTRIM(RIGHT(CONVERT(VARCHAR(30), CAST(STUFF(LEFT(TTime, 4), 3, 0, ':') AS DATETIME), 100), 7)) AS Peso,
dateadd(hour, TTime / 10000, dateadd(minute, TTime % 10000 / 100, dateadd(second, TTime % 100, 0))) AS khtan
FROM @Sample[/code]

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

Delinda
Constraint Violating Yak Guru

315 Posts

Posted - 2009-11-21 : 20:54:37

both of you are great

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources