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

Convert both field into DATETIME :

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

and if you're using [`Getdate()`](

[To see links please register here]

) use this first:

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

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

Works like a charm

SELECT CAST(CAST(@DateField As Date) As DateTime) + CAST(CAST(@TimeField As Time) As DateTime)

Another way is to use `CONCAT`and `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_

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

<h3>Rationale (kudos to ErikE/dnolan)</h3>

> 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`

<h3>Edit regarding new SQL Server 2008 types</h3>
`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)`

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

Have a look at [How to combine date and time to datetime2 in SQL Server?][1] to prevent loss of precision using SQL Server 2008 and up.


[1]:

[To see links please register here]

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

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;

This was my solution which ignores the date value of the time column
```sql
CAST(Tbl.date as DATETIME) + CAST(CAST(Tbl.TimeFrom AS TIME) as DATETIME)
```
Hope this helps others

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)

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