Stanton Stanton - 6 months ago 21
SQL Question

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, etc.

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

If they're not, WHY NOT, and to get a meaningful answer, you'll need to tell us what types they are and what format the data is stored in. Or just fix the table.