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.
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
CollectionTime to char sequences, combine them, and then convert them to a
The parameters to
expression and the optional
style (see syntax documentation).
The date and time
112 converts to an ISO
yyyymmdd format. The
108 converts to
hh:mi:ss format. Evidently both are 8 characters long which is why the
CHAR(8) for both.
The resulting combined char sequence is in format
yyyymmdd hh:mi:ss and then converted to a