SQL add datetime add hour add minute

Question

My table has a DateTime , Hour column.

example: 2012-05-14 00:00:00.000 and 1230

How to add this Hour column to my DateTime column, so I can have

 2012-05-14 12:30:00.000 

I tried with this:

 SELECT DATE_DEBUT, HEURE_DEBUT, DATEADD(hour, CONVERT(int, SUBSTRING(HEURE_DEBUT, 0, 2)), DATE_DEBUT) AS DateTemp, DATEADD(hour, CONVERT(int, SUBSTRING(HEURE_DEBUT, 2, 2)), DateTemp) AS DateComplete FROM ESPTEMPS_PROGRAMMATION 

but that will not work.

thank you in advance Stef

Answer 1

From what I understand, you want to add the first two digits as an hour and the second as minutes, but you do not do this in your DATEADD calls - you add both parts as HOUR - try this instead:

 SELECT DATE_DEBUT, HEURE_DEBUT, DATEADD(MINUTE, CONVERT(int, SUBSTRING(HEURE_DEBUT, 3, 2)), DATEADD(HOUR, CONVERT(int, SUBSTRING(HEURE_DEBUT, 1, 2)), DATE_DEBUT)) FROM ESPTEMPS_PROGRAMMATION 

Here I use two nested DATEADD - internal DATEADD adds hours, external adds minutes to the result of adding hours.

Also: SUBSTRING in SQL Server 1 , for example. the first character of the string is at position 1 (not 0, as you assume)