Get the Datetime
-
Comments posted to this topic are about the item Get the Datetime
-
Rest in peace, indeed, dear ol' friend. You were definitely one of the good guys and you are sorely missed.
--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) -
Nice question, thanks Steve
RIP, Dwain. you taught us well.
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex” -
be aware, that adding dates / times only works with the DATETIME data type but not with the new (ANSI compatible) DATETIME2 (this would cause Error 8117: Operand data type datetime2 is invalid for add operator.)
God is real, unless declared integer.
- Thomas Franz wrote:
be aware, that adding dates / times only works with the DATETIME data type but not with the new (ANSI compatible) DATETIME2 (this would cause Error 8117: Operand data type datetime2 is invalid for add operator.)
Hopefully people will understand that that's the precise reason why the question involved the explicit conversion to the DATETIME datatype for both the date and the time elements.
It's also why I don't use DATETIME2... it can't do what DATETIME does. DATETIME2 actually violates the ANSI standard for being able to add/subtract dates and times.
--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) -
Even though you can do math on datetimes, don't do that. A datetime is not numeric, it is a data structure. Use the proper function for the type.
- GeorgeCopeland wrote:
Even though you can do math on datetimes, don't do that. A datetime is not numeric, it is a data structure. Use the proper function for the type.
Actually, it IS numeric behind the scenes and the direct date math you can do makes life real easy. The "proper function for the type" is sometimes an addition or subtraction operator.
--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) -
I disagree Jeff, date math is a bad idea for any reason. When I have anything to say about it, it is not allowed. Maybe you know what you are doing when you do it. If so, you are the only programmer I have ever met who did. Developers ignoring standards and doing date math is what caused a lot of Y2K problems. You might notice that the ability to do math operators was never provided for DATETIME2 for very good reasons. They can't take away the ability to do math on datetimes, which is a pity, but I wish that they could.
For DATETIME2 the only thing I miss is the ability to set it to zero to get an initialized date.
-
Well if not reading the manual is reason to remove people's ability to code, then there are a LOT of developers to fire out there. As with many other disciplines not paying attention to rules and/or standards isn't a good reason to block it from everyone's use: if that were true we wouldn't be allowed to have bicycles let alone cars.
That said the logic I've seen most mangled when dealing with date math is how to format the duration between 2 to points in time. Try doing that with a datetime2....
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again? - GeorgeCopeland wrote:
I disagree Jeff, date math is a bad idea for any reason. When I have anything to say about it, it is not allowed. Maybe you know what you are doing when you do it. If so, you are the only programmer I have ever met who did. Developers ignoring standards and doing date math is what caused a lot of Y2K problems. You might notice that the ability to do math operators was never provided for DATETIME2 for very good reasons. They can't take away the ability to do math on datetimes, which is a pity, but I wish that they could. For DATETIME2 the only thing I miss is the ability to set it to zero to get an initialized date.
No sir. Y2K was BECAUSE Developers were following "standards" and a pot wad of "we don't need to fix it yet". Unfortunately, the standard was based on the conservation of memory and disk space and so the "standard" was 2 digit years except for some "smart" folks that included the century as a single digit 0 or 1 (which I actually hated). They also followed the wrong standard for leap years, which also caused a Y2K problem for a lot of people. Everyone "knew" the "standard" was "Must be evenly divisible by 4 unless it's also divisible by 100". They forgot the other part about "is also a leap year if evenly divisible by 400".
As for not being able to do direct math on DATETIME2, the problem there was actually because of all the highly opinionated rhetoric about not doing direct math on temporal datatypes. Since it IS a little more difficult (and I do mean very little)) to pull off, MS probably thought it was OK and so skipped the ability to do direct temporal math. Of course, if you're going to talk about people not following "standards", MS seriously failed with DATE, TIME, and DATETIME2 because the ANSI/ISO standards explicitly state that EndDateTime-StartDateTime=Duration and StartDateTime+Duration=EndDateTime and several other temporal math methods.
While I agree that a whole lot of people shouldn't be allowed anywhere near temporal calculations, such ignorance shouldn't be the reason that the rest of us have to be fettered. MS also realized they made a huge mistake with DATETIME2 and that's a large part of the reason why they came out with DATEDIFF_BIG. What they really should have done, though, is follow the ANSI/ISO standards and not only allow direct temporal math but facilitate it!
And I think that anyone that doesn't know that SomeDateTime+1 is the same time the next day should be drummed out of the business. 😀
--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) -
Thanks for the substantive reply, Jeff. I already handled your objections by saying that you were the only developer I knew who knew enough about the subject to reliably do date math. Everybody else, well at least me and everyone who works for me, use the proper functions for the type, period. If you want to cobble custom code to hack datatypes, please do that somewhere else.
-
Thanks for the feedback, George. And, yes, I understand that you already handled my objections and, no, I'm not trying to change your convictions. A lot of people could be watching this thread, though, and I don't want them to think that direct temporal math is a sin (as so many people do). You do. 😀
--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) -
Got it Jeff. Of course date math is not a sin. I put it in the category of, when you get a really hard to troubleshoot bug, and you finally figure it out, it is in the category of "what the heck were you thinking?"
-
I DO agree that we have our own personal experiences to contend with. Ironically, I find that people that don't know how to use date math make some pretty complicated stuff that's actually a bit difficult to troubleshoot because of all the functions they've had to use.
A simple example would be to have someone generate a million row test table with random dates and times without using some form of direct temporal math especially if the range of dates/times need to be constrained. (Actually, I don't think that can be done without some form of direct date math... you do have to use a random number generator and constrain the values somewhere in that mix).
If you want to have some fun, create a million row table with each row having a reasonable StartDateTime and an EndDateTime where the span is random but less that a couple of years. Then ask them to calculate the total duration represented in the table and display it as HHHHHHHHHH:mi:ss:mmm in one column and decimal hours in another using SQL Server 2014 or less and without using any direct date math.
And, just to be sure, in no way, shape, or form am I suggesting that the temporal functions be totally avoided. In fact, life would be a bugger without them.
Shifting gears to a problem that we can probably both agree on, I have to fill out electronic time cards just like everyone else in the company does. The electronic time sheets accept only decimal hours and allows for up to 2 decimal places. I'm also required to be accurate because my time is expensive. I may finish 3 minor tasks (5 minutes each) for 3 different clients in just 15 minutes. But I can't track the time that finitely because the stupid computer driven time sheets will only accept quarter hour increments. For that 15 minutes, I have to charge 45 minutes of time, total. Then people dealing with the time cards ask if I really worked 15 hours for a given day for manpower planning purposes. <headdesk>
Or, I might work on a single task for a single client for 38 minutes but I have to enter the time as 45 minutes because it was longer than half of the quarter hour mark.
And, the fact that I might start at 38 minutes after the hour and end 17 minutes after the next hour means that I have to do the mental calculation of adding 22 minutes to 17 minutes to come up with 39 minutes and then have to remember to bump that up to 45 minutes because it, too, went past the halfway mark of a quarter hour.
Personally, I think all of that is pretty lame in this day and age .
--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) -
I have a suggestion for you Jeff. Your company may bill by the quarter hour, but that doesn't mean that you have to enter your time that way. Tell your finance department that you want to submit your time in minutes and they can figure out the billing quarter hours. This has the added advantage that then you can teach them how to do the date math. 🙂
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply