问题描述
在我正在处理的摘录中，我有 2 个 datetime 列.如图所示，一列存储日期，另一列存储时间.
如何查询表格以将这两个字段合并为 1 列类型为 datetime?
日期
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 部分始终为零 - 并且您的
Time列的Date 部分也始终为零(基准日期:1900 年 1 月 1 日)
添加它们会返回正确的结果.
SELECT Combined = MyDate + MyTime FROM MyTable基本原理(感谢ErikE/dnolan)
<块引用>由于日期存储为两个 4 字节的方式，它的工作原理是这样的Integers，左边 4 字节是 date，右边4 字节是 time.就像在做 $0001 0000 + $0000 0001 =$0001 0001
关于新的SQLServer2008类型的编辑
Date 和 Time 是 SQL Server 2008 中引入的类型.如果你坚持添加，可以使用 Combined = CAST(MyDate AS DATETIME) + CAST(MyTime AS DATETIME)
Edit2关于SQLServer2008及更高版本中的精度损失(感谢MartinSmith)
看看 如何要在 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 partof yourDatecolumn is always zero - and the
Date partof yourTimecolumn is also always zero (base date: January 1, 1900)
Adding them returns the correct result.
SELECT Combined = MyDate + MyTime FROM MyTable
Rationale(kudostoErikE/dnolan)
It works like this due to the way the date is stored as two 4-byte
Integerswith the left 4-bytes being thedateand the right 4-bytes being thetime. Its like doing$0001 0000 + $0000 0001 = $0001 0001
EditregardingnewSQLServer2008types
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)
Edit2regardinglossofprecisioninSQLServer2008andup(kudostoMartinSmith)
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的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！