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.

which DBMS are you using? is it MS SQL Server or MySQL or some other? — Deep, Nov 20 '14 at 10:40
You can simply add the two together - `SELECT CONVERT(DATETIME, '2009-10-10 00:00:00:000', 120) + CAST('10:30' AS TIME)` — GarethD, Nov 20 '14 at 10:45
I tries it and got the following error:the data types datetime and time are incompatible in the add operator — Omri, Nov 20 '14 at 10:51
Hmm it would appear they are compatible in SQL Server 2008, but not in SQL Server 2012. You would need to cast your time to `DATETIME` instead of `TIME` - `SELECT CONVERT(DATETIME, '2009-10-10 00:00:00:000', 120) + CAST('10:30' AS DATETIME)` — GarethD, Nov 20 '14 at 10:58

Deep · Accepted Answer · 2014-11-20T10:50:35.240

3

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)

edited Nov 20 '14 at 10:50

answered Nov 20 '14 at 10:45

Deep

May I know the reason of down vote. Is something wrong in this then I would really like to know.. :) thanks – Deep Nov 20 '14 at 10:51
Well, it wasn't me to vote your answer down, but it just checked it and it's works properly. Thanks! – Omri Nov 20 '14 at 11:13

score 0 · Answer 2 · answered Nov 20 '14 at 10:53

0

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

answered Nov 20 '14 at 10:53

Allan S. Hansen

2 Answers2