如何在SQL Server中将Integer数据类型的列值转换为datetime

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.

评论
  • Oo沁
    Oo沁 回复

    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 ...