Convert into 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

General SQL Server Forums

New to SQL Server Programming

Convert into time

Author

Topic

Robowski
Posting Yak Master

101 Posts

Posted - 2013-09-20 : 10:24:09

The books online conversion chart seems to suggest you can conver varchar into time but when I try I always get an error

Msg 241, Level 16, State 1, Line 3
Conversion failed when converting date and/or time from character string.

Can this be done as a varchar column?

IF OBJECT_ID ('Tab1', 'U') IS NOT NULL
DROP TABLE Tab1;
GO

CREATE TABLE Tab1
(Col1 varchar(6) NULL);
GO

INSERT INTO Tab1
VALUES ('121212')
INSERT INTO Tab1
VALUES ('40512')
INSERT INTO Tab1
VALUES ('101112')
INSERT INTO Tab1
VALUES ('31250');
GO

SELECT
Col1
,CONVERT(TIME, Col1) AS [Time]
FROM
Tab1

sigmas
Posting Yak Master

172 Posts

Posted - 2013-09-20 : 10:46:42

see my solution on this topic
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=188354

sigmas
Posting Yak Master

172 Posts

Posted - 2013-09-20 : 10:59:13

new version

select
cast
(
cast(right('0'+reverse(substring(reverse(next_run_time),5,2)),2) as char(2)) + ':' +
cast(reverse(substring(reverse(next_run_time),3,2)) as char(2)) + ':' +
cast(right(next_run_time,2) as char(2)) as time
) [time1],

cast
(
left(value,2)+':'+
substring(value,3,2)+':'+
substring(value,5,2) as time
) [time2]

from
(
values
('230000'),
('73000'),
('70000'),
('230520'),
('70001')
)d(next_run_time)
cross apply (values(right('0'+next_run_time,6))) c(value)

Robowski
Posting Yak Master

101 Posts

Posted - 2013-09-20 : 11:33:49

So it will work but needs the : in it! cool I'll create a function to stuff : the right places.

Cheers mate! thought maybe it needed some sort of standard at the end like dates need for format.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-09-20 : 14:50:46

[code]DECLARE @Sample TABLE
(
Data INT
);

INSERT @Sample
(
Data
)
VALUES (121212),
( 40512),
(101112),
( 31250);

SELECT TIMEFROMPARTS(Data / 10000, Data / 100 % 100, Data % 100, 0, 0)
FROM @Sample;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources