Sql-server – Combine datetime + string to make a proper datetime

All output of the case expression must be of the same datatype.

If maintaining the XML datatype for the valid XML data is worth a little manipulation of the invalid data, then one workaround could be to wrap the invalid data up like a declaration before casting it to XML:

cast('<?invalid ' + RunLog + ' ?>' as xml)

Also, you shouldn't need to use both try_cast and cast in the same expression.

coalesce(try_cast(RunLog as xml),cast('<?invalid ' + RunLog + ' ?>' as xml))

This seems to work and keep the precision as well:

SELECT DATEADD(day, DATEDIFF(day,'19000101',@D), CAST(@T AS DATETIME2(7)))

The CAST to DATETIME2(7) converts the TIME(7) value (@T) to a DATETIME2 where the date part is '1900-01-01', which is the default value of date and datetime types (see datetime2 and the comment^* at CAST and CONVERT page at MSDN.)

^{* ... When character data that represents only date or only time components is cast to the datetime or smalldatetime data types, the unspecified time component is set to 00:00:00.000, and the unspecified date component is set to 1900-01-01.}

The DATEADD() and DATEDIFF()function take care of the rest, i.e. adding the difference in days between the 1900-01-01 and the DATE value (@D).

Test at: SQL-Fiddle

As noticed by @Quandary, the above expression is considered not-determninistic by SQL Server. If we want a deterministic expression, say because it is to be used for a PERSISTED column, the '19000101'^** needs to be replaced by 0 or CONVERT(DATE, '19000101', 112):

CREATE TABLE date_time
( d DATE NOT NULL,
  t TIME(7) NOT NULL,
  dt AS DATEADD(day, 
                DATEDIFF(day, CONVERT(DATE, '19000101', 112), d), 
                CAST(t AS DATETIME2(7))
               ) PERSISTED
) ;

^{**: DATEDIFF(day, '19000101', d) isn't deterministic as it does an implicit conversion of the string to DATETIME and conversions from strings to datetime are deterministic only when specific styles are used.}