[sql] Combining (concatenating) date and time into a datetime

Using SQL Server 2008, this query works great:

select CAST(CollectionDate as DATE), CAST(CollectionTime as TIME)
from field

Gives me two columns like this:

2013-01-25  18:53:00.0000000
2013-01-25  18:53:00.0000000
2013-01-25  18:53:00.0000000
2013-01-25  18:53:00.0000000
    .
    .
    .

I'm trying to combine them into a single datetime using the plus sign, like this:

select CAST(CollectionDate as DATE) + CAST(CollectionTime as TIME)
from field

I've looked on about ten web sites, including answers on this site (like this one), and they all seem to agree that the plus sign should work but I get the error:

Msg 8117, Level 16, State 1, Line 1
Operand data type date is invalid for add operator.

All fields are non-zero and non-null. I've also tried the CONVERT function and tried to cast these results as varchars, same problem. This can't be as hard as I'm making it.

Can somebody tell me why this doesn't work? Thanks for any help.

SELECT CONVERT(DATETIME, CONVERT(CHAR(8), date, 112) + ' ' + CONVERT(CHAR(8), time, 108))
  FROM tablename

DECLARE @ADate Date, @ATime Time, @ADateTime Datetime

SELECT @ADate = '2010-02-20', @ATime = '18:53:00.0000000'

SET @ADateTime = CAST   (
    CONVERT(Varchar(10), @ADate, 112) + ' ' +   
    CONVERT(Varchar(8), @ATime) AS DateTime)

SELECT @ADateTime [A nice datetime :)]

This will render you a valid result.

dealing with dates, dateadd must be used for precision

declare @a DATE = getdate()
declare @b time(7) = getdate()
select @b, @A, GETDATE(), DATEADD(day, DATEDIFF(day, 0, @a), cast(@b as datetime2(0)))

Concat date of one column with a time of another column in MySQL.

SELECT CONVERT(concat(CONVERT('dateColumn',DATE),' ',CONVERT('timeColumn', TIME)), DATETIME) AS 'formattedDate' FROM dbs.tableName;

An easier solution (tested on SQL Server 2014 SP1 CU6)

Code:

DECLARE @Date date = SYSDATETIME();

DECLARE @Time time(0) = SYSDATETIME();

SELECT CAST(CONCAT(@Date, ' ', @Time) AS datetime2(0));

This would also work given a table with a specific date and a specific time field. I use this method frequently given that we have vendor data that uses date and time in two separate fields.

I am using SQL Server 2016 and both myDate and myTime fields are strings. The below tsql statement worked in concatenating them into datetime

select cast((myDate + ' ' + myTime) as datetime) from myTable

drop table test

create table test(
    CollectionDate date NULL,
    CollectionTime  [time](0) NULL,
    CollectionDateTime as (isnull(convert(datetime,CollectionDate)+convert(datetime,CollectionTime),CollectionDate))
-- if CollectionDate is datetime no need to convert it above
)

insert test (CollectionDate, CollectionTime)
values ('2013-12-10', '22:51:19.227'),
       ('2013-12-10', null),
       (null, '22:51:19.227')

select * from test

CollectionDate  CollectionTime  CollectionDateTime
2013-12-10      22:51:19        2013-12-10 22:51:19.000
2013-12-10      NULL            2013-12-10 00:00:00.000
NULL            22:51:19        NULL

The simple solution

SELECT CAST(CollectionDate as DATETIME) + CAST(CollectionTime as DATETIME)
FROM field

Solution (1): datetime arithmetic

Given @myDate, which can be anything that can be cast as a DATE, and @myTime, which can be anything that can be cast as a TIME, starting SQL Server 2014+ this works fine and does not involve string manipulation:

CAST(CAST(@myDate as DATE) AS DATETIME) + CAST(CAST(@myTime as TIME) as DATETIME)

You can verify with:

SELECT  GETDATE(), 
        CAST(CAST(GETDATE() as DATE) AS DATETIME) + CAST(CAST(GETDATE() as TIME) as DATETIME)

Solution (2): string manipulation

SELECT  GETDATE(), 
        CONVERT(DATETIME, CONVERT(CHAR(8), GETDATE(), 112) + ' ' + CONVERT(CHAR(8), GETDATE(), 108))

However, solution (1) is not only 2-3x faster than solution (2), it also preserves the microsecond part.

See SQL Fiddle for the solution (1) using date arithmetic vs solution (2) involving string manipulation

This works in SQL 2008 and 2012 to produce datetime2:

declare @date date = current_timestamp;
declare @time time = current_timestamp;

select 
@date as date
,@time as time
,cast(@date as datetime) + cast(@time as datetime) as datetime
,cast(@time as datetime2) as timeAsDateTime2
,dateadd(dayofyear,datepart(dayofyear,@date) - 1,dateadd(year,datepart(year,@date) - 1900,cast(@time as datetime2))) as datetime2;

Cast it to datetime instead:

select CAST(CollectionDate as DATETIME) + CAST(CollectionTime as TIME)
from field

This works on SQL Server 2008 R2.

If for some reason you wanted to make sure the first part doesn't have a time component, first cast the field to date, then back to datetime.