Converting varchar(6) in HHmmss format to sql time format

sql sql-server-2012 time-format
Vishnu Babu picture Vishnu Babu · Aug 30, 2016 · Viewed 14k times · Source

How is it possible to convert something stored in DB as varchar(6) (e.g., 180000 which is actually in a format of HHmmss) to time, I tried the following but wont work 

select convert(time,'180000')
select cast('180000' as time)

Answer

Shnugo picture Shnugo · Aug 30, 2016

One more approach

DECLARE @t VARCHAR(6)='180000';

SELECT CAST(STUFF(STUFF(@t,5,0,':'),3,0,':') AS time)

Thanks to @SebtHU, who pointed out, that this would not work with leading zeros in times such as 09:15:00 or 00:45:00. You can use this instead:

SELECT CAST(STUFF(STUFF(RIGHT(CONCAT('000000',@t),6),5,0,':'),3,0,':') AS time);

Related questions

Convert Date format into DD/MMM/YYYY format in SQL Server

I have a query in SQL, I have to get a date in a format of dd/mm/yy Example: 25/jun/2013. How can I convert it for SQL server?

Read More
Recover unsaved SQL query scripts

How to recover the unsaved scripts if the SSMS crashes / unsaved tab gets accidentally closed?

Read More
SQL Server IF EXISTS THEN 1 ELSE 2

Using Sql Server 2012. I have a stored procedure and part of it checks if a username is in a table. If it is, return a 1, if not, return a 2. This is my code: IF EXISTS (SELECT * FROM tblGLUserAccess WHERE GLUserName =…

Read More