我有两列:
date(nvarchar) - it looks like '2015-04-15 00:00:00.000'
time(nvarchar) - looks like '1899-12-30 11:20:25.000'
我需要合并到一个列(datetime)。我怎么能做到呢?
首先,不要将日期/时间值存储为字符。但是,您可以转换它们并添加它们:
select cast(datechar as datetime) + cast(right(timechar, 12) as time)
我才意识到。我不记得默认日期/时间格式是否理解小数秒。以下内容更安全:
select (convert(datetime, datechar, 121) +
convert(time, convert(datetime, timechar, 121))
) as datetimeval
添加了第三列(例如[FullDate]
)之后,您可以使用下面的代码:
UPDATE TABLE
SET [FullDate] = DATEADD(SECOND, SECOND(TIME), DATEADD(MINUTE, MINUTE(TIME), DATEADD(HOUR, HOUR(TIME), DATE)))
或者,您可以像@GordonLinoff提到的那样做,但要确保将time
部分转换为datetime
。像这样:
UPDATE TABLE
SET [FullDate] = CAST([date] as DATETIME) + CAST([time] AS DATETIME)