March 13, 2013 at 11:26 am
Hello Everyone
I have been working most of the morning to try and figure out how I can convert a string to a time format. So far, I have not found anything that will allow this in SQL Server.
So, I have come up with another way, unless someone has a nifty convert function in their toolbox.
I have a string that is taken from a file name. And I would like to store that in time format column in a table. I am already storing the date portion, which seems to convert just fine.
This is my example of a string that I have:
012345
I need to insert a colon after every 2 digits, just like a time would be formatted.
01:23:45
I can easily convert this to a Time(0) format. As long as the colons are in place.
But, if for some odd reason, the string would happen to come by as '12345', missing the leading zero.
How can I handle that so that the time format would still be correct?
Either way, I need this to be able to be converted to a Time(0) format.
Thank You in advance for all your assistance, suggestions, and comments.
Andrew SQLDBA
March 13, 2013 at 11:31 am
Hi Andrew,
I just discovered this undocumented function recently:
declare @timestring varchar(10) = '12345'
select cast(msdb.dbo.agent_datetime('19000101', @timestring) as time(0))
Any good?
Cheers
Gaz
March 13, 2013 at 11:49 am
or you can use something like this:
DECLARE @time TABLE (StringTime VARCHAR(6))
INSERT @time
SELECT '112345'
UNION SELECT '12345'
UNION SELECT '2345'
UNION SELECT '345'
UNION SELECT '45'
UNION SELECT '5'
UNION SELECT ''
;WITH cte AS
(
SELECT RIGHT('000000' + StringTime, 6) AS FixLenStrTime -- make your time to have 6 digits always
FROM @time
)
SELECT CAST(STUFF(FixLenStrTime,3,2,':' + SUBSTRING(FixLenStrTime,3,2) + ':') AS TIME(0)) -- now it will be time!
FROM cte
ORDER by 1 desc
March 13, 2013 at 11:59 am
Or:
DECLARE @time TABLE (StringTime VARCHAR(6))
INSERT @time
SELECT '112345'
UNION SELECT '12345'
UNION SELECT '2345'
UNION SELECT '345'
UNION SELECT '45'
UNION SELECT '5'
UNION SELECT ''
;WITH cte AS
(
SELECT RIGHT('000000' + StringTime, 6) AS FixLenStrTime -- make your time to have 6 digits always
FROM @time
)
SELECT cast(stuff(stuff(FixLenStrTime,5,0,':'),3,0,':') as time(0))
FROM cte
ORDER by cast(stuff(stuff(FixLenStrTime,5,0,':'),3,0,':') as time(0)) desc
March 13, 2013 at 12:19 pm
Thank You Everyone
All your suggestions and samples worked perfectly.
Andrew SQLDBA
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy