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

Question

In an extract I am dealing with, I have 2 fields. One field 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 date?

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

Answer 1

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

Answer 2

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

If you're using SQL Server 2008 then you can combine the columns like this:

SELECT CAST(CAST(your_date_column AS DATE) AS DATETIME) +
    CAST(your_time_column AS TIME)
FROM your_table

For earlier versions of SQL Server then this is what you'll need:

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

Answer 3

You can simply add the two.

• If the Time part of the Date is always zero
• and if the Date part of the Time is also always zero (1899-12-30)

Adding the two gives you a correct result.

SELECT Combined = MyDate + MyTime FROM MyTable

Answer 4

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!

Answer 5

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) 

Answer 6

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)