SQL add Datetime add hour add Minute
23,162
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)
Author by
user609511
Updated on July 09, 2022Comments
-
user609511 almost 2 years
In my table I have
DateTime
,Hour
column.example :
2012-05-14 00:00:00.000
and1230
How can I add this
hour
column into myDatetime
column, so I can have2012-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