I don't really like using the datediff function for this. Sometimes, I'll use Where datevalue1 between dateadd(month,-12,datevalue2) and datevalue2 instead. But make sure both are the same date type.
Same with manipulating any field value. Just had a quick win where they were using Col1 +Col2 = parameter. Replaced with Col1 = Left(param,2) and Col2 = Right(param, Len(param)-2). A couple of other bits and now runs is 0 seconds. Quick Q - I had to change the param type to VARCHAR from CHAR as in one scenario (**default) it would return 0 but the other 30k+ scenarios it matched. Why would that be?? Ta
This is not a dumb discussion.... the datediff function is disappointing. I was hoping you had a clearcut alternative but i see that we are all in the same boat...At my shop, we do a lot of 1-3 year date comparisons so I always have to remember to date compare with the dateAdd function instead. On microsoft's end, it seems that this could all be fixed with a simple change of the the field type. DATEDIFF(YEAR, '07/12/2024', '07/11/2025') returning ~0.99 instead of = 1 seemed like it would solve "most" of the frustration.
Recounting your takeaway points at the end was highly appreciated. Helps solidify your content in mind.
In this episode Master Sorcerer Erik Darling demonstrates his mastery of time. He also puts on his robe and wizard hat.
Just don’t call me Dr. Whom.
I don't really like using the datediff function for this.
Sometimes, I'll use
Where datevalue1 between dateadd(month,-12,datevalue2) and datevalue2 instead. But make sure both are the same date type.
00:26 I am increasingly convinced that Erik Darling is a 4chan poster.
> implying
Oracle: date - interval '1' month. Oh that produces invalid dates. Was confusing in March when it tried to find the 30th Feb.
Ooh I remember running into that trying to produce sample data in Oracle! Quite frustrating!
Same with manipulating any field value. Just had a quick win where they were using
Col1 +Col2 = parameter. Replaced with
Col1 = Left(param,2) and Col2 = Right(param, Len(param)-2). A couple of other bits and now runs is 0 seconds.
Quick Q - I had to change the param type to VARCHAR from CHAR as in one scenario (**default) it would return 0 but the other 30k+ scenarios it matched. Why would that be?? Ta
Perhaps a strange implicit conversion? Hard to say without an example.
This is not a dumb discussion.... the datediff function is disappointing. I was hoping you had a clearcut alternative but i see that we are all in the same boat...At my shop, we do a lot of 1-3 year date comparisons so I always have to remember to date compare with the dateAdd function instead. On microsoft's end, it seems that this could all be fixed with a simple change of the the field type. DATEDIFF(YEAR, '07/12/2024', '07/11/2025') returning ~0.99 instead of = 1 seemed like it would solve "most" of the frustration.
That’s an interesting idea! It can be quite a hassle to check for accurate differences with tiny units.