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
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
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
Mysql Split Comma Separated String into Temp Table
How to Update Millions of Records in MySQL
Database Corruption With Mariadb:Table Doesn't Exist in Engine
Select to Get Rows Based on Minimum Value of a Column
How to Select the Last Record from MySQL Table Using SQL Syntax
Update Multiple Rows in a Table from Another Table When Condition Exists
Convert Julian Date to Yyyy-Mm-Dd
Check If Column Exists Before Executing Oracle
Replace Null Value With Previous Available Value in Row SQL Server Query
Using a Select Statement for Columns With Spaces in It
Sql Select Count for Multiple Columns in a Single Query
How to Limit Results of a Left Join
Select Distinct Records on a Join
Mysql: Alter Table If Column Not Exists
Mysql Convert Date from Mm-Dd-Yyyy to Yyyy-Mm-Dd Format
Find a Matching Value in Date Range and Return Value