Sql Convert Int to Time

SQL convert int to time

Assuming your input will always be an int, you can parse it with something like:

DECLARE @stringTime varchar(6)

SET @stringTime = RIGHT('000000' + CAST(intTime AS VARCHAR), 6)

SELECT CAST(LEFT(@stringTime, 2) + ':' + RIGHT(LEFT(@stringTime, 4), 2) AS TIME) as TimeValue

I'd DEFINITELY look to change this field to an actual time or datetime field, as this level of conversion is not advised, especially for a heavily used database. There's just really got to be a better way to store your data.

Using an int value this way allows for a lot of bad data, without adding a lot of additional checks and/or constraints on your inputs (i.e.: 260000, 127900, etc.)

Convert Int into time

Please try this solution, As @Gordon Linoff gives the solution you can archive your requirement by using timefromparts() function, you also can get the result as per your need as I explained as below.

Solution 1: Time With AM/PM.

DECLARE @IntValue INT  = 1516

SELECT CONVERT(VARCHAR, timefromparts(@IntValue / 100, @IntValue % 100, 0, 0, 0), 100) AS Time

OutPut

enter image description here

Solution 2: Time Without AM/PM.

DECLARE @IntValue INT  = 1516

SELECT LEFT(CONVERT(VARCHAR, timefromparts(@IntValue / 100, @IntValue % 100, 0, 0, 0), 100) ,PATINDEX('%[a,p]m',CONVERT(VARCHAR, timefromparts(@IntValue / 100, @IntValue % 100, 0, 0, 0), 100) )-1) AS Time

Output

enter image description here

Convert Integer value into Hour value (AM / PM) in SQL Server

SELECT FORMAT(DATEADD(hh,val,'00:00:00'),'hh:mm tt') 
FROM YourTable

Convert Int to Time with int value having hour and minute

Edit: Oh, you are on MSSql - leaving this here, if MySQL-Users come along in the future :)

The Idea should apply for MsSQL as well.


You should change your int-columns content, so it contains the second of the day i.e.

0      for 00:00:00
21.600 for 06:00:00
86.399 for 23:59:59

MySQL:
Then you could just use

TIME_FORMAT(SEC_TO_TIME(seconds),'%H:%i')

and perform any maths required way easier.

http://sqlfiddle.com/#!9/9eecb/14236

MsSQL:
Then you could just use

 FORMAT(DATEADD(ss,seconds,0), 'HH:mm')

and perform any maths required way easier.

http://sqlfiddle.com/#!18/9eecb/2639


As your int-columns cant be changed - I would stick with the idea of using seconds of the day - and you can convert your representation, by multiplying the first 2 digits with 3600 and the last 2 digits with 60:

Declare @t int
SET @t=903
SELECT FORMAT(DATEADD(ss,
LEFT(@t,LEN(@t)-2) * 3600 +
RIGHT(@t,2) * 60
,0), 'HH:mm')

http://sqlfiddle.com/#!18/9eecb/2654

edit: Something simpliefied would work as well, as there is actually no conversion required (This just wouldn't return a datetime type, but a string type):

Declare @t int
SET @t=903
SELECT LEFT(@t,LEN(@t)-2) + ':' + RIGHT(@t,2)

But note the missing leading 0, when using this approach.

http://sqlfiddle.com/#!18/9eecb/2653

How to convert int number into time - SQL

Use CONVERT() -- (http://dev.mysql.com/doc/refman/5.7/en/charset-convert.html)

You'll have to pad it with zeros to show CONVERT() that it is in hours and not minutes.

Specific to your example:

> SELECT CONVERT(CONCAT('1000','00'), TIME) AS time1;
time1
10:00:00

or

> SELECT CONVERT(CONCAT(`fieldname`,'00'), TIME) AS time1 FROM `tablename`;
time1
10:00:00

Convert int to time

Should be something along these lines:

UPDATE UnnamedTable SET MergeDT = DATEADD(hour,  [time] / 100,
DATEADD(minute,[time] % 100,DATE_TAKEN))

Re: confusing data and representations. The datetime data types in SQL Server store the data in a compact binary representation. They don't store any formatting information. I don't think anyone would be surprised that the following:

declare @t int
set @t = 0x0D
print @t

Prints 13 rather than 0x0D, and yet people seem to be perpetually surprised that the same logic applies to the datetime types - they stored the data, in a single consistent format, rather than storing the string representation that was used to set their value.

Convert an Integer to time

You could do something like this.

select cast(DATEADD(hour, 13, 0) as time)

The upside is that it will still work even with negative numbers or values over 24.

Convert Int to time and represent the result as time "HH:mm:ss" (accept more than 24h)

You need to ensure your case expression returns the same data type in all eventualities.

Your else probably needs to be '0' or '00:00:00' depending on your requirements.

How to convert an integer (time) to HH:MM:SS::00 in Progress SQL

Based on an answer I have given before:

select
to_char(
timestampadd(
sql_tsi_second,
nrHoraRegistro,
curdate()
),
'hh24:mi:ss'
)
from
pub.testtable


Related Topics



Leave a reply



Submit