如何将一个字段中的日期与另一个字段中的时间相结合 - MS SQL Server

How to combine date from one field with time from another field - MS SQL Server

提问by Jon Winstanley

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

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

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

如何查询表以将这两个字段组合成 1 列类型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

回答by Lieven Keersmaekers

You can simply add the two.

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

• ifthe Time partof your Datecolumn is always zero
• andthe Date partof your Timecolumn is also always zero (base date: January 1, 1900)

• 如果在Time part你的Date列始终为零
• 并且在Date part你的Time栏也始终为零（基准日：1900年1月1日）

Adding them returns the correct result.

添加它们会返回正确的结果。

SELECT Combined = MyDate + MyTime FROM MyTable

Rationale (kudos to ErikE/dnolan)

基本原理（感谢 ErikE/dnolan）

It works like this due to the way the date is stored as two 4-byte Integerswith the left 4-bytes being the dateand the right 4-bytes being the time. Its like doing $0001 0000 + $0000 0001 = $0001 0001

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

Edit regarding new SQL Server 2008 types

编辑关于新的 SQL Server 2008 类型

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

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

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

Edit2 关于 SQL Server 2008 及更高版本的精度损失（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.

看看如何在 SQL Server 中将日期和时间与 datetime2 结合？防止在使用 SQL Server 2008 及更高版本时丢失精度。

回答by LukeH

If the time element of your date column andthe date element of your time column are both zero then Lieven's answeris what you need. If you can't guarantee that will always be the case then it becomes slightly more complicated:

如果您的日期栏的时间要素和你的时间列的日期元素均为零，然后利芬的答案是你所需要的。如果您不能保证情况总是如此，那么它会变得稍微复杂一些：

SELECT DATEADD(day, 0, DATEDIFF(day, 0, your_date_column)) +
    DATEADD(day, 0 - DATEDIFF(day, 0, your_time_column), your_time_column)
FROM your_table

回答by Jojje

This is an alternative solution without any char conversions:

这是一个没有任何字符转换的替代解决方案：

DATEADD(ms, DATEDIFF(ms, '00:00:00', [Time]), CONVERT(DATETIME, [Date]))

You will only get milliseconds accuracy this way, but that would normally be OK. I have tested this in SQL Server 2008.

这样您只会获得毫秒精度，但这通常是可以的。我已经在 SQL Server 2008 中对此进行了测试。

回答by biso

This worked for me

这对我有用

CAST(Tbl.date as DATETIME) + CAST(Tbl.TimeFrom AS TIME)

(on SQL 2008 R2)

（在 SQL 2008 R2 上）

回答by CraigTP

If you're not using SQL Server 2008 (i.e. you only have a DateTime data type), you can use the following (admittedly rough and ready) TSQL to achieve what you want:

如果你没有使用 SQL Server 2008（即你只有一个 DateTime 数据类型），你可以使用以下（公认的粗略和准备好的）TSQL 来实现你想要的：

DECLARE @DateOnly AS datetime
DECLARE @TimeOnly AS datetime 

SET @DateOnly = '07 aug 2009 00:00:00'
SET @TimeOnly = '01 jan 1899 10:11:23'


-- Gives Date Only.
SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @DateOnly))

-- Gives Time Only.
SELECT DATEADD(Day, -DATEDIFF(Day, 0, @TimeOnly), @TimeOnly)

-- Concatenates Date and Time parts.
SELECT
CAST(
    DATEADD(dd, 0, DATEDIFF(dd, 0, @DateOnly)) + ' ' +
    DATEADD(Day, -DATEDIFF(Day, 0, @TimeOnly), @TimeOnly)           
as datetime)

It's rough and ready, but it works!

这是粗糙和准备好的，但它的工作原理！

回答by Pramod Pallath Vasudevan

1. If both of your fields are datetime then simply adding those will work.

   eg:

   Declare @d datetime, @t datetime
   set @d = '2009-03-12 00:00:00.000';
   set @t = '1899-12-30 12:30:00.000';
   select @d + @t
   

2. If you used Date & Time datatype then just cast the time to datetime

   eg:

   Declare @d date, @t time
   set @d = '2009-03-12';
   set @t = '12:30:00.000';
   select @d + cast(@t as datetime)
   

1. 如果您的两个字段都是日期时间，那么只需添加这些字段即可。

   例如：

   Declare @d datetime, @t datetime
   set @d = '2009-03-12 00:00:00.000';
   set @t = '1899-12-30 12:30:00.000';
   select @d + @t
   

2. 如果您使用日期和时间数据类型，那么只需将时间转换为日期时间

   例如：

   Declare @d date, @t time
   set @d = '2009-03-12';
   set @t = '12:30:00.000';
   select @d + cast(@t as datetime)
   

回答by SPE109

Convert the first date stored in a datetime field to a string, then convert the time stored in a datetime field to string, append the two and convert back to a datetime field all using known conversion formats.

将存储在日期时间字段中的第一个日期转换为字符串，然后将存储在日期时间字段中的时间转换为字符串，将两者附加并转换回日期时间字段，所有这些都使用已知的转换格式。

Convert(datetime, Convert(char(10), MYDATETIMEFIELD, 103) + ' ' + Convert(char(8), MYTIMEFIELD, 108), 103) 

回答by Alex Briones

Convert both field into DATETIME :

将两个字段都转换为 DATETIME ：

SELECT CAST(@DateField as DATETIME) + CAST(@TimeField AS DATETIME)

and if you're using Getdate()use this first:

如果您正在使用，请先Getdate()使用它：

DECLARE @FechaActual DATETIME = CONVERT(DATE, GETDATE());
SELECT CAST(@FechaActual as DATETIME) + CAST(@HoraInicioTurno AS DATETIME)

回答by Tom

I had many errors as stated above so I did it like this

如上所述，我有很多错误，所以我这样做了

try_parse(concat(convert(date,Arrival_date),' ',arrival_time) as datetime) AS ArrivalDateTime

It worked for me.

它对我有用。

回答by Kristian Radolovic

SELECT CAST(your_date_column AS date) + CAST(your_time_column AS datetime) FROM your_table

Works like a charm

奇迹般有效