Which is the end?

Question

Which is the end?

Steve Jones - SSC Editor

SSC Guru

Points: 734418
More actions
October 28, 2017 at 11:28 am

#77746

Comments posted to this topic are about the item Which is the end?

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 1

I still think it hilarious (I laughed almost hysterically when I first heard of it) , in a dreadful sort of way, that they took the time to create a function to return the final date of any given month but nothing explicit to find the first of the month. If they had, especially with the optional parameter, they wouldn't have actually needed an EOMONTH function and we wouldn't have to explain to folks why they shouldn't use BETWEEN for temporal range criteria. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Stewart "Arturius" Campbell SSC Guru Points: 72486 More actions · Answer 2

Nice question to start the week on, thanks Steve

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”

Umer Akram SSC-Insane Points: 22209 More actions · Answer 3

Nice question Steve. Thanks for being easy on start of the week 🙂

Ed Wagner SSC Guru Points: 287024 More actions · Answer 4

It's a nice function that fills a hole, but one I've always used the first day of the next month minus 3 ms to include the time. I guess EOMONTH's return date could be converted to a datetime. I'll admit I'm curious about the performance differences. I'll do some testing after I get caught up with work. It's on the list; it might even be fun. :hehe:

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Luis Cazares SSC Guru Points: 183698 More actions · Answer 5

Jeff Moden - Saturday, October 28, 2017 2:10 PM
I still think it hilarious (I laughed almost hysterically when I first heard of it) , in a dreadful sort of way, that they took the time to create a function to return the final date of any given month but nothing explicit to find the first of the month. If they had, especially with the optional parameter, they wouldn't have actually needed an EOMONTH function and we wouldn't have to explain to folks why they shouldn't use BETWEEN for temporal range criteria. 😉

I guess that it's because every month starts at the 1st, but the end can vary from month to month and sometimes depending on the year.
However, I do agree that your proposed function would be given a better use.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 6

Ed Wagner - Monday, October 30, 2017 6:32 AM
It's a nice function that fills a hole, but one I've always used the first day of the next month minus 3 ms to include the time. I guess EOMONTH's return date could be converted to a datetime. I'll admit I'm curious about the performance differences. I'll do some testing after I get caught up with work. It's on the list; it might even be fun. :hehe:

Heh... ok... so you're one the folks I may have to explain the BETWEEN thing to. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

TomThomson SSC Guru Points: 104773 More actions · Answer 7

Jeff Moden - Monday, October 30, 2017 4:31 PM
Ed Wagner - Monday, October 30, 2017 6:32 AM
It's a nice function that fills a hole, but one I've always used the first day of the next month minus 3 ms to include the time. I guess EOMONTH's return date could be converted to a datetime. I'll admit I'm curious about the performance differences. I'll do some testing after I get caught up with work. It's on the list; it might even be fun. :hehe:
Heh... ok... so you're one the folks I may have to explain the BETWEEN thing to. 😉

EOMonth is badly named, I think. After all, the end of the month and the data of the last day of the month are not the same thing. For example the last day of October 2017 is 2017-10-31 but the end of he month October 2017 occurrs at 2017-11-01T00:00:00, a tatetime (or datetime2) not a date.
And the reason that "between" doesn't go well with datetime2 (or datetime) is that it's a pain in the but to have to think (and type) 2017-10-31T23:59:59.9999999 (or 2017-10-31T23:59:59.997) or a different number of 9s after the stop for datetime2 with non-default precision (eg datetime2(5) would need five 9s after that decimal point instead of the 7 for datetime2 with default precision (which is datetime2(7)). Personally I find it a pain in the butt to have to write "B <= D and D < E" instead of the usual math notation "B <= D < E" but most programming languages are pretty horrible so I put up with it (and there's no way i'm going to write all those 997s or strings of 9s just so I can use use "between"); and I also imagine that all hell can break lose if someone decides that they want to hold more precise times so that instead of datetime2(2) something is changed to datetime2(3) and as a result every use of "between" has to be changed (because somewhere in something there's a between where the 9s count won't be changed wlthough thy should have been and other places where they count was got right, potentially leading to mutually inconsistent reports being generated.

Tom

Ed Wagner SSC Guru Points: 287024 More actions · Answer 8

Jeff Moden - Monday, October 30, 2017 4:31 PM
Ed Wagner - Monday, October 30, 2017 6:32 AM
It's a nice function that fills a hole, but one I've always used the first day of the next month minus 3 ms to include the time. I guess EOMONTH's return date could be converted to a datetime. I'll admit I'm curious about the performance differences. I'll do some testing after I get caught up with work. It's on the list; it might even be fun. :hehe:
Heh... ok... so you're one the folks I may have to explain the BETWEEN thing to. 😉

Probably not. I get the way it works and already do the >= and < consistently in the WHERE clause. I was wondering about the performance differences in calculation using the new EOMONTH function, adding a day and subtracting 3 ms to obtain the same datetime value as I use now. Given the performance of some newer functions, I'm interested in what I'll find with this one.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

Jeff Moden SSC Guru Points: 1003853 More actions · Answer 9

Ed Wagner - Tuesday, October 31, 2017 5:40 AM
Jeff Moden - Monday, October 30, 2017 4:31 PM
Ed Wagner - Monday, October 30, 2017 6:32 AM
It's a nice function that fills a hole, but one I've always used the first day of the next month minus 3 ms to include the time. I guess EOMONTH's return date could be converted to a datetime. I'll admit I'm curious about the performance differences. I'll do some testing after I get caught up with work. It's on the list; it might even be fun. :hehe:
Heh... ok... so you're one the folks I may have to explain the BETWEEN thing to. 😉
Probably not. I get the way it works and already do the >= and < consistently in the WHERE clause. I was wondering about the performance differences in calculation using the new EOMONTH function, adding a day and subtracting 3 ms to obtain the same datetime value as I use now. Given the performance of some newer functions, I'm interested in what I'll find with this one.

Just pulling your chain because you mentioned the -3ms thing. 😉 My thought was, "Will the real Ed Wagner please stand up"?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ed Wagner SSC Guru Points: 287024 More actions · Answer 10

Jeff Moden - Tuesday, October 31, 2017 7:59 AM
Ed Wagner - Tuesday, October 31, 2017 5:40 AM
Jeff Moden - Monday, October 30, 2017 4:31 PM
Ed Wagner - Monday, October 30, 2017 6:32 AM
It's a nice function that fills a hole, but one I've always used the first day of the next month minus 3 ms to include the time. I guess EOMONTH's return date could be converted to a datetime. I'll admit I'm curious about the performance differences. I'll do some testing after I get caught up with work. It's on the list; it might even be fun. :hehe:
Heh... ok... so you're one the folks I may have to explain the BETWEEN thing to. 😉
Probably not. I get the way it works and already do the >= and < consistently in the WHERE clause. I was wondering about the performance differences in calculation using the new EOMONTH function, adding a day and subtracting 3 ms to obtain the same datetime value as I use now. Given the performance of some newer functions, I'm interested in what I'll find with this one.
Just pulling your chain because you mentioned the -3ms thing. 😉 My thought was, "Will the real Ed Wagner please stand up"?

Ahhh - I see it now. Touche. Honestly, Jeff, I'm just looking for excuses to test the new functions to see if they perform anything like the old ones. 😉
Then again, I need to have the time to do it, which I haven't yet, but I will eventually...or at least I hope to.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

SQLWinther SSCertifiable Points: 6294 More actions · Answer 11

One problem with EOMONTH is that it doesn't return the time, and if you convert the date to datetime, you will not get the end of month as the time will be 00:00:00 and not 23:59:59.
It works for equality operations on dates, but not datatime.

/Håkan Winther
MCITP:Database Developer 2008
MCTS: SQL Server 2008, Implementation and Maintenance
MCSE: Data Platform

drew.allen SSC Guru Points: 76944 More actions · Answer 12

hakan.winther - Thursday, November 2, 2017 7:19 AM
One problem with EOMONTH is that it doesn't return the time, and if you convert the date to datetime, you will not get the end of month as the time will be 00:00:00 and not 23:59:59.
It works for equality operations on dates, but not datatime.

23:59:59 is not the end of the month either. 23:59:59.997 is (assuming DATETIME data type). You should never be using equality (closed) for datetime intervals. You should always use half-closed intervals.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA