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

由硕向槐发布于 2020-05-18 11:59:08

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沁

2020-05-18 11:59:08

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

硕向槐

这家伙很懒，什么都没留下

积分
0
话题
0
评论
3184
注册排名
2146