将日期和时间组合（连接）到日期时间 [英] Combining (concatenating) date and time into a datetime

问题描述

使用SQL Server 2008，此查询功能非常好：

Using SQL Server 2008, this query works great:

select CAST(CollectionDate as DATE), CAST(CollectionTime as TIME)
from field

给我这样的两列：

2013-01-25  18:53:00.0000000
2013-01-25  18:53:00.0000000
2013-01-25  18:53:00.0000000
2013-01-25  18:53:00.0000000
    .
    .
    .

我正在尝试使用加号将它们组合成一个单一的datetime，如下所示： p>

I'm trying to combine them into a single datetime using the plus sign, like this:

select CAST(CollectionDate as DATE) + CAST(CollectionTime as TIME)
from field

我查看了十个网站，包括本网站的答案（如这个），他们都似乎同意加号可以工作，但是我收到错误：

I've looked on about ten web sites, including answers on this site (like this one), and they all seem to agree that the plus sign should work but I get the error:

消息8117，级别16，状态1，行1

操作数数据类型日期对于add运算符无效。

Msg 8117, Level 16, State 1, Line 1
Operand data type date is invalid for add operator.

所有字段都为非零和非空。我也尝试过CONVERT函数，并尝试将这些结果转换为varchars，同样的问题。这不可能像我一样努力。

All fields are non-zero and non-null. I've also tried the CONVERT function and tried to cast these results as varchars, same problem. This can't be as hard as I'm making it.

有人可以告诉我为什么这不行吗？感谢任何帮助。

Can somebody tell me why this doesn't work? Thanks for any help.

推荐答案

假设底层数据类型是日期/时间/日期时间等。

Assuming the underlying data types are date/time/datetime, etc.

SELECT CONVERT(DATETIME, CONVERT(CHAR(8), CollectionDate, 112) 
  + ' ' + CONVERT(CHAR(8), CollectionTime, 108))
  FROM dbo.whatever;

如果不是， 为什么不 ，并得到一个有意义的答案，你需要告诉我们他们是什么类型和数据存储在什么格式，或者只是修复表。

If they're not, WHY NOT, and to get a meaningful answer, you'll need to tell us what types they are and what format the data is stored in. Or just fix the table.

这篇关于将日期和时间组合（连接）到日期时间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！