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

问题描述

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

如何查询表以将这两个字段合并为 datetime 类型的 1 列?

日期

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

次数

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

解决方案

您可以简单地将两者相加.

• 如果Date 列的时间部分 始终为零
• 和Time 列的Date part 也始终为零(基准日期:1900 年 1 月 1 日)

添加它们返回正确的结果.

SELECT Combined = MyDate + MyTime FROM MyTable

基本原理(感谢 ErikE/dnolan)

<块引用>

由于日期存储为两个 4 字节的方式，它是这样工作的Integers 左边的 4 个字节是 date 右边的4 字节是 time.就像做 $0001 0000 + $0000 0001 =$0001 0001

编辑关于新的 SQL Server 2008 类型

Date 和 Time 是 SQL Server 2008 中引入的类型.如果坚持要添加，可以使用Combined = CAST(MyDate AS DATETIME) + CAST(MyTime AS DATETIME)

关于 SQL Server 2008 及更高版本精度损失的 Edit2(向 Martin Smith 致敬)

看看如何将日期和时间与 SQL Server 中的 datetime2 结合起来? 以防止使用 SQL Server 2008 及更高版本时精度损失.

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

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

Dates

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

Times

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.

• 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

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

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)

Edit2 regarding loss of precision in SQL Server 2008 and up (kudos to Martin Smith)

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屋！