convert varchar(4) to time

Question

Dear.

in database I have char(4) which I need to change into time

for example: in my column i have record like 0900 or 2300...how can I convert that values into time 09:00:00 or 23:00:00

 

thanks for helping me

Answer 1

DECLARE @a char(4) = '0900';
SELECT CAST(STUFF(@a, 3, 0, ':') AS time);

---

Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Answer 2

Hi,

On SQL 2008 and above you can use

declare @i char(4)
select @i = '0900'

select CAST( SUBSTRING(@i,1,2) + ':00' as time(0))

---

-Chintak (My Blog)

Answer 3

Dear.

in database I have char(4) which I need to change into time

for example: in my column i have record like 0900 or 2300...how can I convert that values into time 09:00:00 or 23:00:00

 

thanks for helping me

Hi,

Which version of SQL Server are you using? 

The time data type was first introduced in SQL Server 2008. Prior to SQL Server 2008, we only have datetime and smalldatetime to store date and time values. They have both date and time parts. If we don’t specify a date part, SQL Server will use ‘1900-01-01’ as the date part.

Please see:

DECLARE @t CHAR(4)
SET @t = '0900'

SELECT CONVERT(DATETIME,STUFF(@t,3,0,':')+':00')

For more information, please also refer to: http://www.karaszi.com/SQLServer/info_datetime.asp

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.

Answer 4

Here is another solution.

DECLARE 
@StrTime CHAR(4) = '1600',
@Time TIME
SELECT @Time = STUFF(@StrTime,3,1,':')
SELECT CONVERT(VARCHAR(20), @Time, 100)