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

You can simply add the two.

• if the Time part of your Date column is always zero
• and the Date part of your Time column is also always zero (base date: January 1, 1900)

Adding them returns the correct result.

SELECT Combined = MyDate + MyTime FROM MyTable

Rationale (kudos to ErikE/dnolan)

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

Edit regarding new SQL Server 2008 types

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)

Edit2 regarding loss of precision in SQL Server 2008 and up (kudos to 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.

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:

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

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!

This was my solution which ignores the date value of the time column

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

Hope this helps others

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

Finding this works for two dates where you want time from one and date from the other:

declare @Time as datetime = '2021-11-19 12:34'
declare @Date as datetime = '2021-10-10'
SELECT @time + datediff(day, @Time, @Date)

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

simply concatenate both , but cast them first as below

select cast(concat(Cast(DateField as varchar), ' ', Cast(TimeField as varchar)) as datetime) as DateWithTime from TableName;

select s.SalesID from SalesTbl s 
        where cast(cast(s.SaleDate  as date) as datetime) + cast(cast(s.SaleCreatedDate as time) as datetime) between @FromDate and @ToDate

The existing answers do not address the datetime2 datatype so I will add mine:

Assuming that you want to add a time value to a datetime2 value where:

• The datetime2 value could contain non-zero time component and/or fractional seconds
• The time value could contain the value 23:59:59.9999999 which is 86,399.9999999 seconds, 86,399,999,999.9 microseconds or 86,399,999,999,900 nanoseconds¹

Due to the limitations of dateadd function¹ you must add them in two steps:

• Convert the time value to seconds and use dateadd(second, ...)
• Extract the nanoseconds from the time value and use dateadd(nanosecond, ...) to add them to the date calculated above

declare @dv datetime2 = '2000-01-01 12:34:56.7890123';
declare @tv time = '23:59:59.9999999';
select dateadd(
    nanosecond,
    datepart(nanosecond, @tv),
    dateadd(
        second,
        datepart(hour, @tv) * 60 * 60 + datepart(minute, @tv) * 60 + datepart(second, @tv),
        @dv
    )
);
-- 2000-01-02 12:34:56.7890122

¹ Nanosecond values might not fit in int datatype which dateadd function expects.

I ran into similar situation where I had to merge Date and Time fields to DateTime field. None of the above mentioned solution work, specially adding two fields as the data type for addition of these 2 fields is not same.

I created below solution, where I added hour and then minute part to the date. This worked beautifully for me. Please check it out and do let me know if you get into any issues.

;with tbl as ( select StatusTime = '12/30/1899 5:17:00 PM', StatusDate = '7/24/2019 12:00:00 AM' ) select DATEADD(MI, DATEPART(MINUTE,CAST(tbl.StatusTime AS TIME)),DATEADD(HH, DATEPART(HOUR,CAST(tbl.StatusTime AS TIME)), CAST(tbl.StatusDate as DATETIME))) from tbl

Result: 2019-07-24 17:17:00.000