如何从一个字段与另一个字段的时间组合日期 - MS SQL Server [英] How to combine date from one field with time from another field - MS SQL Server

问题描述

在我处理的一个提取中，我有2个 datetime 列。一列存储日期，另一列存储时间，如图所示。

In an extract I am dealing with, I have 2 datetime columns. One column stores the dates and another the times as shown.

如何查询表格，将这两个字段合并成1列类型为 datetime ？

How can I query the table to combine these two fields into 1 column of type datetime?

日期

2009-03-12 00:00:00.000
2009-03-26 00:00:00.000
2009-03-26 00:00:00.000

次

1899-12-30 12:30:00.000
1899-12-30 10:00:00.000
1899-12-30 10:00:00.000

推荐答案

您可以简单地添加两个。

You can simply add the two.

• 如果 日期时间部分列总是为零


• 和 日期部分 code>列始终为零（基准日期：1900年1月1日）

• if the Time part of your Date column is always zero
• and the Date part of your Time column is also always zero (base date: January 1, 1900)

添加他们返回正确的结果。

Adding them returns the correct result.

SELECT Combined = MyDate + MyTime FROM MyTable

理由（对ErikE / dnolan的赞誉）

Rationale (kudos to ErikE/dnolan)

It works like this due to the way the date is stored as two 4-byte `Integers` with the
left 4-bytes being the `date` and the right 4-bytes being the `time`.  

Its like doing $0001 0000 + $0000 0001 = $0001 0001

新的SQL Server 2008类型

日期和时间在 SQL Server 2008 中引入的类型。如果您坚持添加，您可以使用 Combined = CAST（MyDate AS DATETIME）+ CAST（MyTime AS DATETIME）

Edit regarding new SQL Server 2008 types

Date and Time are types introduced in SQL Server 2008. If you insist on adding, you can use Combined = CAST(MyDate AS DATETIME) + CAST(MyTime AS DATETIME)

看看如何将日期和时间与SQL Server中的datetime2组合，以防止丢失精准使用SQL Server 2008及以上。

Have a look at How to combine date and time to datetime2 in SQL Server? to prevent loss of precision using SQL Server 2008 and up.

这篇关于如何从一个字段与另一个字段的时间组合日期 - MS SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！