June 18, 2025 at 12:00 am
Comments posted to this topic are about the item What is Between?
June 21, 2025 at 1:14 am
I was told a long while ago to avoid using Between, and honestly, although I cannot remember the reason - at the time I was told this the person who told me demonstrated it, I agreed and haven't used it since.
I do remember where I was working, and there we had a lot of trouble because some fields were date and some were datetime (this predates some recent features such as UTC related stuff).
But, the times, they are a changing - and have changed.
Is there any situation today, using a modern version, where I should avoid using Between?
I ask, because I think it is more readable, and better readability leads to fewer mistakes.
Thanks!
June 24, 2025 at 3:14 pm
I've personally never used between and it is because of this exact QOTD - I can never remember for certain if it is inclusive, exclusive, or a mix and I'd rather not do trial and error when I am coding. Using >=, <=, >, or < is MUCH more clear on my intent. And even though I remember how BETWEEN works today, if I stop using it for a while then go back to it in 6 months or a year, I'll be needing to look it up again.
Plus, if you do coding in other languages, they may treat between differently. I know I used to code in C#, Powershell, bat files, VB6, SQL Server, Groovy, and whatever NiceLabel uses (I think it was either VB or Java... can't remember offhand) and if you asked me which of those are inclusive or exclusive when you use BETWEEN, I couldn't tell you. BUT if you asked me to have it pick numbers 1 to 10 inclusive, I know they all would support >= and <=.
Now that I am ONLY doing SQL development (and very minimal of that, I'm more focused on the admin side), I'll probably learn more of the functions like "BETWEEN", but when I needed to switch between a bunch of different languages, I would rather trust my gut than have to jump to the docs to confirm things as I go.
As for the date/datetime issue that JChrisCompton mentioned, I usually try to CAST/CONVERT my datatypes before comparison. So if I am comparing a date to a datetime, I'll cast them both to datetime or date, whichever makes more sense for the problem I am trying to solve. I don't like implicit conversion if I can avoid it for the same reason I don't use BETWEEN. Will SQL convert to date or datetime for me? Offhand, I don't remember, so I'll check the docs. And then you get stuff like this that gets all goofy:
https://www.reddit.com/r/ProgrammerHumor/comments/yf4hid/everyone_says_js_is_weird_with_strings_and/
It makes sense if you understand C mind you... but it is a good example of understanding your language and the exact reason why I like my code to be readable as a first priority, accurate as second priority, and fast as my third priority. If the code isn't readable, I can't hand it over to anyone to take over on support. If it produces incorrect results, it needs to be fixed and thanks to it being readable, fixing it shouldn't be too hard. And "fast" I find is very subjective. If it takes an hour to give a result, yeah, that's too slow. But if it takes 15 seconds and the end user wants it in 10 seconds, 15 is probably close enough.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply