I have 2 tables.
1)Test1
2)Test2
create table test1
(
date1 datetime,
value1 int
)
insert into test1 values('2021-08-01 10:00:00:07',10),('2021-08-01 11:00:00:17',12)
create table test2
(
date2 datetime,
value2 int
)
insert into test2 values('2021-08-01 10:00:00:03',5),('2021-08-01 11:00:00:07',8)
And I want the output as mentioned below
You can change the datetype of the tables to smalldatetime if possible. Otherwise, you can add a smalldatetime column to the existing tables and update it with the date1/date2 values. If neither are possible, then you can do this. But depending on the volume, the performance will be terrible.
Select cast(t1.date1 as smalldatetime) as [DateTime], t1.value1, t2.value2
from #test1 t1
join #test2 t2
on cast(t1.date1 as smalldatetime) = cast(t2.date2 as smalldatetime)
