blocks|key|853487|text|假设底层数据类型为日期/时间/日期时间类型：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|853488|SELECT+CONVERT(DATETIME,+CONVERT(CHAR(8),+CollectionDate,+112)+
++%2B+'+'+%2B+CONVERT(CHAR(8),+CollectionTime,+108))
++FROM+dbo.whatever;|code-block|syntax|javascript|853489|这将将CollectionDate和CollectionTime转换为char序列，组合它们，然后将它们转换为datetime。|offset|length|style|CODE|853490|CONVERT的参数是data_type、expression和可选的style+(参见语法文档)。|853491|style值112转换为ISO+yyyymmdd格式。style值108转换为hh:mi:ss格式。显然，这两个字符都有8个字符长，这就是为什么data_type对两个字符都是CHAR(8)。|853492|生成的组合字符序列是格式yyyymmdd+hh:mi:ss，然后转换为datetime。|853493|entityMap|0|LINK|mutability|MUTABLE|url|https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql#syntax|1|https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql#date-and-time-styles^0|0|0|3|E|I|E|1J|8|0|0|7|B|9|L|A|Z|5|18|4|0|0|0|5|6|3|G|8|R|5|X|3|13|8|20|9|2G|7|0|5|1|0|C|H|Z|8|0^^$0|@$1|2|3|4|5|6|7|12|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|13|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|14|8|@$I|15|J|16|K|L]|$I|17|J|18|K|L]|$I|19|J|1A|K|L]]|9|@]|A|$]]|$1|M|3|N|5|6|7|1B|8|@$I|1C|J|1D|K|L]|$I|1E|J|1F|K|L]|$I|1G|J|1H|K|L]|$I|1I|J|1J|K|L]]|9|@$I|1K|J|1L|1|1M]]|A|$]]|$1|O|3|P|5|6|7|1N|8|@$I|1O|J|1P|K|L]|$I|1Q|J|1R|K|L]|$I|1S|J|1T|K|L]|$I|1U|J|1V|K|L]|$I|1W|J|1X|K|L]|$I|1Y|J|1Z|K|L]|$I|20|J|21|K|L]|$I|22|J|23|K|L]]|9|@$I|24|J|25|1|26]]|A|$]]|$1|Q|3|R|5|6|7|27|8|@$I|28|J|29|K|L]|$I|2A|J|2B|K|L]]|9|@]|A|$]]|$1|S|3|-4|5|6|7|2C|8|@]|9|@]|A|$]]]|T|$U|$5|V|W|X|A|$Y|Z]]|10|$5|V|W|X|A|$Y|11]]]]

Assuming the underlying data types are date/time/datetime types:

<pre><code>SELECT CONVERT(DATETIME, CONVERT(CHAR(8), CollectionDate, 112) 
 + ' ' + CONVERT(CHAR(8), CollectionTime, 108))
 FROM dbo.whatever;
</code></pre>

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

The parameters to <code>CONVERT</code> are <code>data_type</code>, <code>expression</code> and the optional <code>style</code> (see <a href="https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql#syntax" rel="noreferrer">syntax documentation</a>).

The <a href="https://learn.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql#date-and-time-styles" rel="noreferrer">date and time <code>style</code></a> value <code>112</code> converts to an ISO <code>yyyymmdd</code> format. The <code>style</code> value <code>108</code> converts to <code>hh:mi:ss</code> format. Evidently both are 8 characters long which is why the <code>data_type</code> is <code>CHAR(8)</code> for both.

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

blocks|key|2236|text|而是将其转换为datetime：|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2237|select+CAST(CollectionDate+as+DATETIME)+%2B+CAST(CollectionTime+as+TIME)
from+field|code-block|syntax|javascript|2238|这适用于Server+2008+R2。|2239|如果出于某种原因，您希望确保第一部分没有时间组件，请先将字段转换为日期，然后返回到datetime。|2240|entityMap^0|7|8|0|0|0|15|8|0^^$0|@$1|2|3|4|5|6|7|Q|8|@$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|U|8|@]|D|@]|E|$]]|$1|M|3|N|5|6|7|V|8|@$9|W|A|X|B|C]]|D|@]|E|$]]|$1|O|3|-4|5|6|7|Y|8|@]|D|@]|E|$]]]|P|$]]

Cast it to <code>datetime</code> instead:

<pre><code>select CAST(CollectionDate as DATETIME) + CAST(CollectionTime as TIME)
from field
</code></pre>

This works on SQL Server 2008 R2.

If for some reason you wanted to make sure the first part doesn't have a time component, first cast the field to date, then back to <code>datetime</code>.

blocks|key|853530|text|DECLARE+@ADate+Date,+@ATime+Time,+@ADateTime+Datetime

SELECT+@ADate+=+'2010-02-20',+@ATime+=+'18:53:00.0000000'

SET+@ADateTime+=+CAST+++(
++++CONVERT(Varchar(10),+@ADate,+112)+%2B+'+'+%2B+++
++++CONVERT(Varchar(8),+@ATime)+AS+DateTime)

SELECT+@ADateTime+[A+nice+datetime+:)]|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|853531|这将使您得到一个有效的结果。|unstyled|853532|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>DECLARE @ADate Date, @ATime Time, @ADateTime Datetime

SELECT @ADate = '2010-02-20', @ATime = '18:53:00.0000000'

SET @ADateTime = CAST (
 CONVERT(Varchar(10), @ADate, 112) + ' ' + 
 CONVERT(Varchar(8), @ATime) AS DateTime)

SELECT @ADateTime [A nice datetime :)]
</code></pre>

This will render you a valid result.

blocks|key|853548|text|drop+table+test

create+table+test(
++++CollectionDate+date+NULL,
++++CollectionTime++[time](0)+NULL,
++++CollectionDateTime+as+(isnull(convert(datetime,CollectionDate)%2Bconvert(datetime,CollectionTime),CollectionDate))
--+if+CollectionDate+is+datetime+no+need+to+convert+it+above
)

insert+test+(CollectionDate,+CollectionTime)
values+('2013-12-10',+'22:51:19.227'),
+++++++('2013-12-10',+null),
+++++++(null,+'22:51:19.227')

select+*+from+test

CollectionDate++CollectionTime++CollectionDateTime
2013-12-10++++++22:51:19++++++++2013-12-10+22:51:19.000
2013-12-10++++++NULL++++++++++++2013-12-10+00:00:00.000
NULL++++++++++++22:51:19++++++++NULL|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|853549|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>drop table test

create table test(
 CollectionDate date NULL,
 CollectionTime [time](0) NULL,
 CollectionDateTime as (isnull(convert(datetime,CollectionDate)+convert(datetime,CollectionTime),CollectionDate))
-- if CollectionDate is datetime no need to convert it above
)

insert test (CollectionDate, CollectionTime)
values ('2013-12-10', '22:51:19.227'),
 ('2013-12-10', null),
 (null, '22:51:19.227')

select * from test

CollectionDate CollectionTime CollectionDateTime
2013-12-10 22:51:19 2013-12-10 22:51:19.000
2013-12-10 NULL 2013-12-10 00:00:00.000
NULL 22:51:19 NULL
</code></pre>

blocks|key|2247|text|SELECT+CONVERT(DATETIME,+CONVERT(CHAR(8),+date,+112)+%2B+'+'+%2B+CONVERT(CHAR(8),+time,+108))
++FROM+tablename|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|2248|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>SELECT CONVERT(DATETIME, CONVERT(CHAR(8), date, 112) + ' ' + CONVERT(CHAR(8), time, 108))
 FROM tablename
</code></pre>

blocks|key|1929487|text|一个更简单的解决方案(在Server+2014+SP1+CU6上测试)|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1929488|代码：|1929489|DECLARE+@Date+date+=+SYSDATETIME();

DECLARE+@Time+time(0)+=+SYSDATETIME();

SELECT+CAST(CONCAT(@Date,+'+',+@Time)+AS+datetime2(0));|code-block|syntax|javascript|1929490|这也适用于给定一个具有特定日期和特定时间域的表。我经常使用这种方法，因为我们有供应商数据，在两个单独的字段中使用日期和时间。|1929491|entityMap^0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|N|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|O|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|P|8|@]|9|@]|A|$]]|$1|K|3|-4|5|6|7|Q|8|@]|9|@]|A|$]]]|L|$]]

An easier solution (tested on SQL Server 2014 SP1 CU6) 

Code: 

<pre><code>DECLARE @Date date = SYSDATETIME();

DECLARE @Time time(0) = SYSDATETIME();

SELECT CAST(CONCAT(@Date, ' ', @Time) AS datetime2(0));
</code></pre>

This would also work given a table with a specific date and a specific time field. I use this method frequently given that we have vendor data that uses date and time in two separate fields.

blocks|key|1929502|text|简单解|type|unstyled|depth|inlineStyleRanges|entityRanges|data|1929503|SELECT+CAST(CollectionDate+as+DATETIME)+%2B+CAST(CollectionTime+as+DATETIME)
FROM+field|code-block|syntax|javascript|1929504|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

The simple solution

<pre><code>SELECT CAST(CollectionDate as DATETIME) + CAST(CollectionTime as DATETIME)
FROM field
</code></pre>

blocks|key|853602|text|处理日期时，必须使用dateadd来实现精度。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|853603|declare+@a+DATE+=+getdate()
declare+@b+time(7)+=+getdate()
select+@b,+@A,+GETDATE(),+DATEADD(day,+DATEDIFF(day,+0,+@a),+cast(@b+as+datetime2(0)))|code-block|syntax|javascript|853604|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

dealing with dates, dateadd must be used for precision

<pre><code>declare @a DATE = getdate()
declare @b time(7) = getdate()
select @b, @A, GETDATE(), DATEADD(day, DATEDIFF(day, 0, @a), cast(@b as datetime2(0)))
</code></pre>

blocks|key|2275|text|我使用的是Server+2016，myDate和myTime字段都是字符串。下面的tsql语句将它们连接到datetime中。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2276|select+cast((myDate+%2B+'+'+%2B+myTime)+as+datetime)+from+myTable|code-block|syntax|javascript|2277|entityMap^0|H|6|O|6|1H|8|0|0^^$0|@$1|2|3|4|5|6|7|M|8|@$9|N|A|O|B|C]|$9|P|A|Q|B|C]|$9|R|A|S|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|T|8|@]|D|@]|E|$I|J]]|$1|K|3|-4|5|6|7|U|8|@]|D|@]|E|$]]]|L|$]]

I am using SQL Server 2016 and both <code>myDate</code> and <code>myTime</code> fields are strings. The below tsql statement worked in concatenating them into <code>datetime</code>

<pre><code>select cast((myDate + ' ' + myTime) as datetime) from myTable
</code></pre>

blocks|key|853681|text|解(1)：日期时间算法|type|unstyled|depth|inlineStyleRanges|entityRanges|data|853682|给定@myDate+(可以是可以转换为日期的任何内容)和@myTime+(可以转换为时间)，启动Server+2014%2B可以工作得很好，不涉及字符串操作：|853683|CAST(CAST(@myDate+as+DATE)+AS+DATETIME)+%2B+CAST(CAST(@myTime+as+TIME)+as+DATETIME)|code-block|syntax|javascript|853684|您可以通过以下方式进行验证：|853685|SELECT++GETDATE(),+
++++++++CAST(CAST(GETDATE()+as+DATE)+AS+DATETIME)+%2B+CAST(CAST(GETDATE()+as+TIME)+as+DATETIME)|853686|解决方案(2)：字符串操作|853687|SELECT++GETDATE(),+
++++++++CONVERT(DATETIME,+CONVERT(CHAR(8),+GETDATE(),+112)+%2B+'+'+%2B+CONVERT(CHAR(8),+GETDATE(),+108))|853688|然而，溶液(1)不仅比溶液(2)快2-3倍，而且还保留了微秒部分。|853689|参见解(1)使用日期算法+vs+解决方案(2)涉及字符串操作的SQL|offset|length|853690|entityMap|0|LINK|mutability|MUTABLE|url|http://sqlfiddle.com/#!18/6a0a6/1/0|1|http://sqlfiddle.com/#!18/6a0a6/2^0|0|0|0|0|0|0|0|0|2|A|0|G|E|1|0^^$0|@$1|2|3|4|5|6|7|16|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|17|8|@]|9|@]|A|$]]|$1|D|3|E|5|F|7|18|8|@]|9|@]|A|$G|H]]|$1|I|3|J|5|6|7|19|8|@]|9|@]|A|$]]|$1|K|3|L|5|F|7|1A|8|@]|9|@]|A|$G|H]]|$1|M|3|N|5|6|7|1B|8|@]|9|@]|A|$]]|$1|O|3|P|5|F|7|1C|8|@]|9|@]|A|$G|H]]|$1|Q|3|R|5|6|7|1D|8|@]|9|@]|A|$]]|$1|S|3|T|5|6|7|1E|8|@]|9|@$U|1F|V|1G|1|1H]|$U|1I|V|1J|1|1K]]|A|$]]|$1|W|3|-4|5|6|7|1L|8|@]|9|@]|A|$]]]|X|$Y|$5|Z|10|11|A|$12|13]]|14|$5|Z|10|11|A|$12|15]]]]

Solution (1): datetime arithmetic
Given @myDate, which can be anything that can be cast as a DATE, and @myTime, which can be anything that can be cast as a TIME, starting SQL Server 2014+ this works fine and does not involve string manipulation:
<pre><code>CAST(CAST(@myDate as DATE) AS DATETIME) + CAST(CAST(@myTime as TIME) as DATETIME)
</code></pre>
You can verify with:
<pre><code>SELECT GETDATE(), 
 CAST(CAST(GETDATE() as DATE) AS DATETIME) + CAST(CAST(GETDATE() as TIME) as DATETIME)
</code></pre>
Solution (2): string manipulation
<pre><code>SELECT GETDATE(), 
 CONVERT(DATETIME, CONVERT(CHAR(8), GETDATE(), 112) + ' ' + CONVERT(CHAR(8), GETDATE(), 108))
</code></pre>
However, solution (1) is not only 2-3x faster than solution (2), it also preserves the microsecond part.
See SQL Fiddle for the <a href="http://sqlfiddle.com/#!18/6a0a6/1/0" rel="nofollow noreferrer">solution (1) using date arithmetic</a> vs <a href="http://sqlfiddle.com/#!18/6a0a6/2" rel="nofollow noreferrer">solution (2) involving string manipulation</a>

blocks|key|2262|text|将一列的日期与MySQL中另一列的时间连接起来。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2263|SELECT+CONVERT(concat(CONVERT('dateColumn',DATE),'+',CONVERT('timeColumn',+TIME)),+DATETIME)+AS+'formattedDate'+FROM+dbs.tableName;|code-block|syntax|javascript|2264|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

Concat date of one column with a time of another column in MySQL.

<pre><code>SELECT CONVERT(concat(CONVERT('dateColumn',DATE),' ',CONVERT('timeColumn', TIME)), DATETIME) AS 'formattedDate' FROM dbs.tableName;
</code></pre>

Using SQL Server 2008, this query works great:

<pre><code>select CAST(CollectionDate as DATE), CAST(CollectionTime as TIME)
from field
</code></pre>

Gives me two columns like this:

<pre><code>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
 .
 .
 .
</code></pre>

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

<pre><code>select CAST(CollectionDate as DATE) + CAST(CollectionTime as TIME)
from field
</code></pre>

I've looked on about ten web sites, including answers on this site (like <a href="https://stackoverflow.com/questions/700619/how-to-combine-date-from-one-field-with-time-from-another-field-ms-sql-server">this one</a>), and they all seem to agree that the plus sign should work but I get the error:

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

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.

Combining (concatenating) date and time into a datetime

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

使用Server 2008，这个查询工作得很好：select CAST(CollectionDate as DATE), CAST(CollectionTime as TIME)from field给了我两个像这样的列：2013-01-25  18:53:00.00000002013-01-25  18:53:00.0...

问将日期和时间合并成日期时间
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问将日期和时间合并成日期时间EN