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
– Deep, Commented Nov 20, 2014 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
– GarethD, Commented Nov 20, 2014 at 10:45
I tries it and got the following error:the data types datetime and time are incompatible in the add operator — Omri
– Omri, Commented Nov 20, 2014 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
– GarethD, Commented Nov 20, 2014 at 10:58

Deep · Accepted Answer · 2014-11-20 10:50:35Z

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)

answered Nov 20, 2014 at 10:45

Deep

3,2021 gold badge15 silver badges21 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

May I know the reason of down vote. Is something wrong in this then I would really like to know.. :) thanks

Well, it wasn't me to vote your answer down, but it just checked it and it's works properly. Thanks!

Allan S. Hansen · Accepted Answer · 2014-11-20 10:53:09Z

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, 2014 at 10:53

Allan S. Hansen

4,09126 silver badges25 bronze badges