In my table I have DateTime
, Hour
column.
example : 2012-05-14 00:00:00.000
and 1230
How can I add this hour
column into 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 it does not work.
thanks you in advance, Stev
From what I understand, you want to add the first two digits as hour, the second two as minute - but you're not doing this in your DATEADD
calls - you're adding 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'm using two nested DATEADD
- the inner DATEADD
adds the hours, the outer adds the minutes onto the result of adding the hours.
Also: SUBSTRING
in SQL Server is 1-based, e.g. the first character of a string is at position 1 (not 0, as you seem to assume)