Replace all uses of BETWEEN

[schuemie]

Several queries use the SQL operator BETWEEN, for example this query. Even though this translates without error to the different SQL dialects, it means different things. In some dialects the start and end dates are included. In some dialects the end date isn't included, etc. It is better to be explicit, so instead of

WHERE my_date BETWEEN start_date AND end_date

we should use

WHERE my_date >= start_date AND my_date <= end_date

[PRijnbeek]

Agree. How do we make the community aware of these type of best practices?

[schuemie]

The SqlRender vignette might be a good place to capture best practices related to writing SQL that translates properly to the different dialects?

[PRijnbeek]

Yes absolutely.