In a typical web application, dates are retrieved from the database layer strongly typed (e.g. in c# as a System.DateTime as opposed System.String).
When a date needs to be expressed as a string (e.g. displayed on a page), the conversion from DateTime to string is done in the presentation tier.
Why is this? Why is it a bad thing to convert the DateTime to a string on the database tier?
See also the heated debate in chat, and the original question that started all of this.
Dates, DateTimes and really any other typed object, should generally be left in their properly typed format until the moment you need them to be made into some other type - especially when that type is a human readable form, and especially when it's a lossy/one-way sort of conversion.
Why? Because it is assumed that the type provides you with lots of handy built in functionality, like proper equality testing, addition and subtraction, comparison (greater than, less than), time zone and locale functionality (especially important for anything time-related), etc. If you decide you want to support Americans and the "Month Day[th], Year" format as well as the common British style of "Day Month Year", or the ISO standard of "Year-Month-Day"? What would you do if it was a string and you needed to make that change, parse it back into a Date? Ugh, no thanks - there are many evils and dastardly bugs that way, which are best avoided entirely.
More specifically, you mentioned tiered architecture, which has the presentation layer separate from the data later. This is actually the other big reason to pass a Date as a Date and not a string - because what type of string formatting should the date be put into? English, Chinese, with or without seconds/milliseconds, full month name or digits, will you want to sort on the date field later (sorting on a string demands a certain string format if you want it to work right), etc? This is all a question of presentation - how the user should view the data - and putting that logic anywhere else would limit the advantage of having tiered architecture in the first place. The database should not need to know or care how you'll want to view date in the future.
Finally, nearly all complex applications (which is what tiered architectures are for) that care about time will inevitably use times/dates in many, many different ways, and often at all different levels of the architecture. The typed objects related to times and dates exist for a really good reason: time itself, and especially human calendar systems, are weird and hard. Ultimately times and dates are not strings for the same reason that integers and floating points aren't strings, and it will only make your life harder if you try to pretend they are really just arrays of characters, because they just aren't.
Why? Because it is assumed that the type provides you with lots of handy built in functionality In my opinion this is just secondary. The real reason is that the type tells you what something is. A date is not a string, it just happens to translate easily into a human-readable string.
– Doval
Sep 23 '16 at 15:21
He is saying to use the web server to convert the data time to a string. I am saying do it on the database server and not the web server. Why do you think that is better? - M T Head
I want to know the type.
I really don't care if your database stores information in a string, some ints, or bytes, because, well in the end it's always bytes anyway. That string taking up more space than is needed in your database doesn't bother me. What bothers me is running into dates like this:
11/10/2016
And not knowing if that's the eleventh month or the tenth month.
But it's validated you say. Sure you put it through a validation processes. The date is perfectly correct. But here I am maintaining this thing and all I know is the date is a string. I can't even tell you what date this is.
"Tenth day of November in the two thousand and sixteenth year of our lord."
That's a string. One of our presentations needs it in that format. You said the database converts all dates to strings right? Have fun with that.
The database's job is to store data not present data. Sure, you could do that in strings but then you have to parse it out to make it useful to present for other formats. Storing it in a standard parsed form for whatever type the DB offers gets us as close to ready to present as we can be without having made a presentation decision. It really doesn't matter to me if the DB backs that type with a string or ints or bytes. So long as it knows what it's doing.
But when you don't let the DB know we're dealing with a date and store a date as a string you are prematurely presenting and favoring one presentation over all others. This forces all other presenters to parse before converting. No, the database is not a part of the presentation layer. Don't ask it to be.
Likewise the presentation layer is not part of the database so it's not wise to couple a report to database details. It's far more robust to act on types.
Conversion of date to string for presentation purposes requires knowing the user preferences, since the exact same date generally should be displayed differently for users in different locales. Even if you use a single locale in your application, proper behavior should use the locale of the application instead of the database server; and they're not guaranteed to be identical even if at this moment they coincidentally match.
The conversion from a universal date datatype to a locale-specific string should happen in the presentation layer because it's the layer that knows how that conversion should be performed.
This is undesirable for the same reason you wouldn't just want to blindly convert any type to a string as soon as it hits the application tier. There is a high likelihood you're going to want to use that object in some manner before presenting it to the user (if you even do present it to the user). For this specific example, imagine you needed to do some date math on the object. There is no downside to just converting the object to a string precisely before you display it.
Types exist for a reason, if they added no benefit then we would not have them and would not use them and we would just have "the type" and everything would be that. They are not only convenient they also add safety and efficiency. The following is a list of why you should always persist types in their native format and not as strings. I used DateTime as an example most of the time but the same principles apply for any primitive type like integers, decimals, binary, etc.
Almost all data stores allow to specify constraints on the data, this includes type constraints. One of the main benefits of specifying a DateTime instance is that the stored data will be constrained to that type. It will never be possible to enter anything other than a date time regardless of how the data was inserted into the store. The latter is important for larger systems where there are multiple processes that interact directly with the store. This also includes trying to add faulty dates like February 30, (of any year) as February can only have 29 days on a leap year and 28 days for non leap years.
There are also validation constraints which can be implemented in the Data Store like ensuring that a inserted date does not exceed the current date or that a start date occurs before an end date.
Most data stores also have built in operations / functions like DateAdd or DatePart in MS Sql Server. This allows you to start filtering or selecting specific data while the data is still in the store (not retrieved to the application yet).
By using the native type other developers or systems that also interact with the store do not have to be informed on the minute details of how that primitive type is stored. This is not the case if that type was stored as a string, then you have to ensure that everyone understands the format of that DateTime string representation. This system becomes fragile when dealing with data that spans locales, regions, and cultures in data origin, the physical location of an application, and the attributes of the end user/system that is interacting with that data. Example: the date format in one country might be MM/dd/yyyy (like in the US) but in another it could be dd/MM/yyyy, detecting that difference becomes almost impossible.
Speed of retrieval, speed of validation, speed of operations and storage efficiency are all also important factors. Example of the retrieval speed: data stores allow for indexes on columns and these indexes can generally be more efficiently used if the type is stored in its native format.
Executing queries against the store becomes simpler using the native type system as developers, once again, do not have to guess as to the storage format. Almost all data store application providers (example: ado.net) provide mechanisms for creating the proper parameterized queries based on the native types passed in. Here is an example of adding the Date part to a ado.net query against a Sql Server store, doing the same with strings would be very cumbersome and fragile/error prone.
command.Parameters.Add(new SqlParameter("@startDate", SqlDbType.Date) {Value = myDateInstance.Date});
The native types in code also provide for standard operations like the .net type System.Date. Operations are usually mathematical in nature like adding dates, finding the difference between dates, etc. Again, this is not possible to do easily on string types.
When a primitive type is finally converted to a string in the presentation layer (the correct location in the program stack to do so) the programmer now has various options to display it correctly according to the context in which it is presented. This context generally consists of the actual meaning of the data and the locale of the user.
Example 1A datetime instance can be automatically formatted based on the locale of the user.
DateTime.Now.ToString("D", CultureInfo.GetCultureInfo(userContext.Culture))
A decimal instance could be representing an amount (currency) and the locale of the user should then also display the amount according to their preference. A c# application might then display the value using
amount.ToString("C", CultureInfo.GetCultureInfo(userContext.Culture))
This could be critical as different cultures display numbers differently. In the US period (.) and comma (,) have the exact reverse meaning as in the Netherlands.
This is very specific to DateTime instances. A date and time represents an occurrence at a specific moment in time but this usually has to be conveyed/presented to the user depending on their own time zone. Example: a DateTime instance 2016-09-21T23:38:21.399Z could be displayed as 9/21/2016 5:21 PM for a user in the Eastern Timezone in the USA. There are many ways to accomplish this but it becomes next to impossible if the date time instance is kept in memory as a string type or in the data store as a string type.
The general 2 rules for an application follow when it comes to converting any primitive type to a string representation are as these
There's really nothing wrong with doing this (it's done all the time in services) as long as you are using a non-ambiguous format for your date. By unambiguous, I mean not only is the date clear (e.g. MM/DD vs. DD/MM) but also what timezone it is in. So up-front, if you are going to represent your dates as text, use an ISO format. I strongly prefer UTC based time strings.
Pros:
Cons:
If someone said they wanted to do this, I would ask "why?" because there's not really much point to it. If the reason that someone wants to return the date as a String is because they will just display it directly, this is not a good reason to use Strings from the DB.
