将(连接)日期和时间组合成一个日期时间 [英] Combining (concatenating) date and time into a datetime

问题描述

使用 SQL Server 2008，此查询效果很好:

select CAST(CollectionDate as DATE), CAST(CollectionTime as TIME)来自外地

给我两列这样的:

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

我正在尝试使用加号将它们组合成一个日期时间，如下所示:

select CAST(CollectionDate as DATE) + CAST(CollectionTime as TIME)来自外地

我查看了大约十个网站，包括本网站上的答案(例如 这个)，他们似乎都同意加号应该有效，但我收到错误:

<块引用>

消息 8117，级别 16，状态 1，第 1 行
操作数数据类型日期对于加法运算符无效.

所有字段都非零且非空.我也尝试过 CONVERT 函数并尝试将这些结果转换为 varchars，同样的问题.这不会像我做的那么难.

谁能告诉我为什么这不起作用?感谢您的帮助.

解决方案

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

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

这会将 CollectionDate 和 CollectionTime 转换为字符序列，组合它们，然后将它们转换为 datetime.

CONVERT 的参数是 data_type、expression 和可选的 style(参见 语法文档).

日期和时间 style 值 112 转换为 ISO yyyymmdd 格式.style 值 108 转换为 hh:mi:ss 格式.显然两者都是 8 个字符长，这就是为什么 data_type 是 CHAR(8) 的原因.

得到的组合字符序列采用 yyyymmdd hh:mi:ss 格式，然后转换为 datetime.

Using SQL Server 2008, this query works great:

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

Gives me two columns like this:

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

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:

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

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 types:

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

This will convert CollectionDate and CollectionTime to char sequences, combine them, and then convert them to a datetime.

The parameters to CONVERT are data_type, expression and the optional style (see syntax documentation).

The date and time style value 112 converts to an ISO yyyymmdd format. The style value 108 converts to hh:mi:ss format. Evidently both are 8 characters long which is why the data_type is CHAR(8) for both.

The resulting combined char sequence is in format yyyymmdd hh:mi:ss and then converted to a datetime.

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