SQLTeam.com | Weblogs | Forums

Add a Time column and a Date column together?

Hi,

Please can someone help with. I am trying to create a new column containing a combination of two existing columns.

Column 1 is datetime and column 2 is smallint

Example
2020-11-20 00:00:00.000 900
2020-11-21 00:00:00.000 1700

I want to create a new column which is a comb of Column 1 and 2

End result:
2020-11-20 09:00:00.000
2020-11-21 09:00:00.000

I tried myself with : SELECT
CONVERT(datetime, CONVERT(varchar(10), Column2 / 100)+ ':' + CONVERT(varchar(10), Column2% 100))

This gets a new column as a datetime, but doesn't contain the proper date.

Any help appreciated.

Thanks

hi

i tried to do this .. please see if this is ok ..

i divided by 100 and added hrs

if your data is different this will need to be changed to include minutes

image

select
Col1
, Col2
, dateadd(hh,Col2/100,Col1)
from
@Data

declare @Data table (Col1 DateTime , Col2 int ) 

insert into @Data Values 
('2020-11-20 00:00:00.000',900	),
('2020-11-21 00:00:00.000',1700	)
SELECT column1, column2, 
    DATEADD(MINUTE, column2 / 100 * 60 + column2 % 100, column1) AS column1_plus_column2
FROM ( VALUES
    (CAST('2020-11-20 00:00:00.000' AS datetime), CAST(900 AS int)),
    ('2020-11-21 00:00:00.000', 1700),
    ('2020-11-22 00:00:00.000', 33),
    ('2020-11-23 00:00:00.000', 2144)
) test_data(column1, column2)

Just curious, is there a chance that the engine will calculate wrong value
due to missing parenthesis.
I mean
(column2 / 100 * 60) + column2 % 100

I don't believe so. Operator precedence is such that the % should be first, then the /, then the * and finally the +. But you're correct, it is probably cleaner and better to explicitly specify parentheses:

(column2 / 100 * 60) + (column2 % 100)

1 Like

Thanks for your responses, this is what worked:

DATEADD(mi,Time/100*60+Time%100,TheDate)