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
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)
Mateen
September 30, 2020, 9:21pm
4
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)