SQLTeam.com | Weblogs | Forums

How to ignore milliseconds and grasp column values from different table into single table

I have 2 tables.

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)

1 Like