September 3, 2015 at 4:54 am
There are a 'proper' number of ways to store date's and times even within SQL-server.
Yes indeed - date, time, datetime, datetime2 (and possibly others). These are the "proper" data types that I think Lynn had in mind. The point is to use one of those, and not to attempt to render dates as integers, decimals or character strings.
And although this is a SQL Server forum, SQL Server is not isolated from the rest of the world.
That's true - I dealt with that by suggesting that you make the conversion at the point at which data enters or leaves SQL Server. You can also not make the conversion at all and leave your application layer to handle the presentation.
And how dates and times are stored internally is important, because that also describes what can be done and can not be done with dates and times.
It's very important, yes, but not for the reason you mentioned. It's important because if you use a "proper" data type, arithmetic is easier, invalid dates can't be entered, ordering happens in a predictable way and storage requirements are usually lower. If you want to do something that "can not be done" with a "proper" data type, by all means convert to another data type before doing it. Just don't store it like that.
The example of Excel was specifically used. Why? In Excel when you store a date/time or a time, you can not store 08:00 exactly. (In SQL server you can).
That sounds like a limitation of Excel, not a reason to change the way we do things in SQL Server.
Comparing a datetime field (I have seen this multiple times) with something like 20090626 23:59:59.999 is a bad idea, why because of the way the data is stored and rounded.
Good point. SQL Server 2008 and later has datetime2 - use that if three-decimal-point millisecond precision is important (and probably also even if it isn't).
For many problems there is often more than one solution. Although a number of the solutions might be bad, there is often also a number of 'proper' solutions.
Yes, I agree that there is more than one date/time data type, and it's important to choose the correct one. I may have implied in my previous post that there's a single data type for dates instead of a single set of data types.
For example within the older SQLserver versions there where a number of ways to implement time only. So some solutions used a string to store time. While other used numbers to store time and still others used a datetime format (where the date was set to a specific value or was ignored).
I would argue that in such cases, the last option is the only "proper" one, for the reasons I mentioned above. Thankfully, though, it's not a problem in any currently supported version of SQL Server, so we don't have to worry about it as much as we used to.
By the way, I use inverted commas for "proper" because I'm quoting the term originally used by Lynn. I don't mean it to come across as sarcastic, condescending, facetious or patronising.
John
Viewing post 16 (of 15 total)
You must be logged in to reply to this topic. Login to reply