Convert VarChar 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 2008 Forums

Transact-SQL (2008)

Convert VarChar to Time

Author

Topic

baburk
Posting Yak Master

108 Posts

Posted - 2009-03-13 : 02:34:23

The below query returns

SELECT REPLACE( RIGHT( REPLICATE('0',5 - LEN(5)) + CONVERT(VARCHAR(18), RB.Batch), 5) + '.00', '.', ':')
FROM RunningBatch AS RB

08:02:00
10:00:00
06:00:00

How can I able to cast this to sys.Time

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-13 : 03:37:45

You can't. First record has an hour of 80 which is not allowed since there are only 24 hours every day.

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

baburk
Posting Yak Master

108 Posts

Posted - 2009-03-13 : 04:01:23

quote:
Originally posted by Peso

You can't. First record has an hour of 80 which is not allowed since there are only 24 hours every day.

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

I had edited my post. Time is within in 24 hours

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-13 : 04:20:31

SELECT cast(REPLACE( RIGHT( REPLICATE('0',5 - LEN(5)) + CONVERT(VARCHAR(18), RB.Batch), 5) + '.00', '.', ':') as time)
FROM RunningBatch AS RB

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

baburk
Posting Yak Master

108 Posts

Posted - 2009-03-13 : 04:38:56

quote:
Originally posted by Peso

SELECT cast(REPLACE( RIGHT( REPLICATE('0',5 - LEN(5)) + CONVERT(VARCHAR(18), RB.Batch), 5) + '.00', '.', ':') as time)
FROM RunningBatch AS RB

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

Thanks it works

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources