Steven Willis (3/5/2013)
These values "10:35","14:22", etc. are from an HTML form and they are posted as strings generated by a hard-coded HTML dropdown with the 5 character pseudo-"time" values as an option item in the select input. So they are just numeric-looking character strings until someone (me) does something with them. The list of "times" is not dynamically generated as the HTML form is just a static hard-coded form.
So, there is no actually a time value sent from application.
It's a string which can be parsed to a time value sent along with another string which can be parsed to a date value.
It's purely you choice to store them separately, not any kind of requirement coming from the business case.
Poor choice, I totally agree with Jeff here:
Jeff Moden (12/24/2012)
I also have a hard time understanding why people would want to separate date and time even for display purposes unless they are, in fact, just trying to display duration. Certainly, I wouldn't store date and time separately.
Now I wrote in my post above that I know the date these values apply to, but that date (also a string since this is HTML we are talking about) needs conversion also and I COULD IF I WANTED TO concatenate the date and the times to create a well-formed datetime value but as you have correctly surmised the date itself IS stored in another column already.
Yes, as I said - it was your choice.
As for the "time" column all I wanted to do was give the "times" back to the application in a manner that they would sort as time and not in ASCII sequence and allow me to determine durations between the values.
As I showed in my post, simple use of CONVERT function will allow to do just that.
CONVERT is actually better as it gives better control over the formatting of output strings.
I just didn't need to bother with the date-part nor did I care about the date-part since all of the time values in a particular batch ALWAYS belong to the same day. (Golfers don't have tee times that cross midnight into the next day. Maybe if the course was in the arctic I'd have to worry about that.)
You still need to know to WHICH DAY any particular tee break applies.
So, you actually do need to bother with the date-part.
Otherwise you would not need to store it in the database.
The post was originally made to show that there are rare cases like this where the date part doesn't matter.
Turns out - your example does not show such a case.
If that wasn't the case why would Microsoft have bothered creating a TIME datatype?
It's not the only moronic feature MS added to the product.
There must be a reason why MS designers named them "F... me nodes".
I don't know what your issue is. I made an observation concerning a situation I encountered and if you never face such an issue then I guess the whole point is moot. I don't want to argue about it.
I have no issues with that.
Apparently you have.
Because it's you who posted the problem which caused only by your belief that you've got a case when separating date and time in a database is a right thing to do.
What was the solution to you problem?
Right, concatenating date and time back together.
Only "advantage" MS provided to you by introducing TIME data type is that you're gonna do concatenation every time you run a query to display the schedule or produce a report instead of doing it once when saving the data in the database.
I'm not gonna use (hopefully :-P) you application, so I don't have an issue with that.
As for your users - they'll have to put up with sluggish performance of the application.
But they'll learn.