CODEWITHSUNDEEP
datetimesql-server-2012type-conversion
user13564417
user13564417

Reputation: 13

How can I convert an Integer datatype columns value into datetime in SQL Server

I have the table as in the attached screenshot and I want to concatenate the logdate and logtime integer columns and convert them into datetime

logdate  | logtime
20200501 |   90205
20200502 |  100215
20200503 |  110105

like '2020-05-01 09:02:05' for the first example.

Upvotes: 0

Views: 70

Answers (1)

Bohemian
Bohemian

Reputation: 425288

Build up a valid date string, then convert() it:

select
    convert (datetime, concat(
        substring(cast(logdate as char(8)), 1, 4), '-', 
        substring(cast(logdate as char(8)), 5, 2), '-', 
        substring(cast(logdate as char(8)), 7, 2), ' ',
        substring(right('000000' + cast(logtime as varchar(6)), 6), 1, 2), ':', 
        substring(right('000000' + cast(logtime as varchar(6)), 6), 3, 2), ':', 
        substring(right('000000' + cast(logtime as varchar(6)), 6), 5, 2)
    ) as timestamp
from ...

Upvotes: 1

Related Questions