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.

Answer

Assuming the underlying data types are date/time/datetime types:

SELECT CONVERT(DATETIME, CONVERT(CHAR(8), CollectionDate, 112) 
  + ' ' + CONVERT(CHAR(8), CollectionTime, 108))
  FROM dbo.whatever;

This will convert CollectionDate and CollectionTime to char sequences, combine them, and then convert them to a datetime.

The parameters to CONVERT are data_type, expression and the optional style (see syntax documentation).

The date and time style value 112 converts to an ISO yyyymmdd format. The style value 108 converts to hh:mi:ss format. Evidently both are 8 characters long which is why the data_type is CHAR(8) for both.

The resulting combined char sequence is in format yyyymmdd hh:mi:ss and then converted to a datetime.