The best way to join the use date field

Question

I know I read somewhere that using a function as a join, like year (date), to make a join in another table whose year is not the best way to join.

I basically have a table with a year field with a value of 2016 and another table with an actual date like 01/01/2016, which I use year (date) to make a connection.

It would be good practice to create a field as 2016, for example, it would usually start in a financial period, to have a field for the start date 01/04/2016 and the end date 31/03/2016 and use these fields to make the connection. But it was interesting how it would look in terms of Join? or will I continue to use my method of converting 01/01/2016 to a year using the year () function?

Answer 1

No, don’t do anything special. Your problem can be easily solved by changing the problem.

The problem with your current implementation is that the function must execute on every row of the joined table for every row in the main table, which has 2 unpleasant effects:

• he is slow. You must execute the function nxm times
• you cannot use the index (if there is one and should be) in the datetime column of the joined table

But there is an easy way to avoid all this muck. Calculate the beginning and end of the year as the time from the year value and use it to join another table in your datetime between these two values. It:

• Only calculate start and end dates once per row in the main table.
• allow the use of the index on the datetime column (you must add it if it is not already)

I don’t really understand that you are right enough to use real code, so here is what the pseudo-request will look like:

select * from table1 join table2 on table2.datetime between <calculate start of year from table1.year> and <calculate end of year from table1.year> 

Answer 2

One thing you can do is define a calculated column and create an index for it:

 alter table t add start_year as (year(start_date)); create index idx_t_??_start_year on t(??, start_year); 

?? Designed for one or more other columns. An index on start_year will not be very selective, so SQL Server may not use it.

Answer 3

Hi, instead of risking adding a new column, use the substring function. Take the required column from the two tables. In the join condition, specify the condition as column_Table1 = substring (column_Table2,6,10) if your date format is dd / mm / yyyy