Adding varchar value and datetime in MS SQL

Question

I need some help with a query.

I have the following columns:

Fulltime (datetime)
Hour (varchar)

I need to convert the Hour into time format, and add it into the Fulltime

For example:

Fulltime = 2009-10-10 00:00:00:000
Hour = 10:30
Result = 2009-10-10 10:30:00:000

I found many ways to convert varchar to a datetime, but it always add a date into the result.

Answer 1

Cast Hour as datetime and add it in Fulltime like :

Select Fulltime + CAST(Hour as DATETIME)

and if you want the remove existing time part and add the hour then :

SELECT Cast(Cast(Fulltime AS DATE) AS DATETIME)
       + Cast(Hour AS DATETIME) 

Answer 2

Try something like this:

SELECT '2009-10-10 00:00:00:000' + CONVERT(DATETIME, CAST ('10:30' AS time))

And replace the string representations with your variables/columns