How to combine date from one field with time from

2019-01-01 06:42发布

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

14条回答
刘海飞了
2楼-- · 2019-01-01 07:15
DECLARE @Dates table ([Date] datetime);
DECLARE @Times table ([Time] datetime);

INSERT INTO @Dates VALUES('2009-03-12 00:00:00.000');
INSERT INTO @Dates VALUES('2009-03-26 00:00:00.000');
INSERT INTO @Dates VALUES('2009-03-30 00:00:00.000');

INSERT INTO @Times VALUES('1899-12-30 12:30:00.000');
INSERT INTO @Times VALUES('1899-12-30 10:00:00.000');
INSERT INTO @Times VALUES('1899-12-30 10:00:00.000');

WITH Dates (ID, [Date])
AS (
    SELECT ROW_NUMBER() OVER (ORDER BY [Date]), [Date] FROM @Dates
), Times (ID, [Time])
AS (
    SELECT ROW_NUMBER() OVER (ORDER BY [Time]), [Time] FROM @Times
)
SELECT Dates.[Date] + Times.[Time] FROM Dates
    JOIN Times ON Times.ID = Dates.ID

Prints:

2009-03-12 10:00:00.000
2009-03-26 10:00:00.000
2009-03-30 12:30:00.000
查看更多
君临天下
3楼-- · 2019-01-01 07:15

Another way is to use CONCATand CAST, be aware, that you need to use DATETIME2(x) to make it work. You can set x to anything between 0-7 7 meaning no precision loss.

DECLARE @date date = '2018-03-12'
DECLARE @time time = '07:00:00.0000000'
SELECT CAST(CONCAT(@date, ' ', @time) AS DATETIME2(7))

Returns 2018-03-12 07:00:00.0000000

Tested on SQL Server 14

查看更多
余生请多指教
4楼-- · 2019-01-01 07:18

This worked for me

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

(on SQL 2008 R2)

查看更多
弹指情弦暗扣
5楼-- · 2019-01-01 07:23

To combine date from a datetime column and time from another datetime column this is the best fastest solution for you:

select cast(cast(DateColumn as date) as datetime) + cast(TimeColumn as datetime) from YourTable
查看更多
步步皆殇っ
6楼-- · 2019-01-01 07:26
SELECT CAST(your_date_column AS date) + CAST(your_time_column AS datetime) FROM your_table

Works like a charm

查看更多
何处买醉
7楼-- · 2019-01-01 07:30

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.

查看更多
登录 后发表回答