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

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

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:

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!

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

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)

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)

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.

This worked for me

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

(on SQL 2008 R2)

If the time element of your date column *and* the date element of your time column are both zero then [Lieven's answer][1] is 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


[1]:

[To see links please register here]

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

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.