SQL add Datetime add hour add Minute

Question

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

marc_s · Accepted Answer · 2011-11-05 14:51:14Z

4

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)

edited Nov 5, 2011 at 14:51

answered Nov 5, 2011 at 14:24

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

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

Collectives™ on Stack Overflow

SQL add Datetime add hour add Minute

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related