How much column formatting should be used in a native SQL query?

Question

Ever since I started using an ORM for my day to day data access. I've started to think about how much I should rely formatting functions for my columns. By formatting functions, I mean such things as Oracle's decode(), instr() and initcap().

Example

Say I'm selecting this column using formatting in Oracle.

(to_number(to_char(to_date('1', 'J') + (EndTime - StartTime), 'J') - 1) * 24)
 + (to_char(to_date('00', 'HH24') + (EndTime - EndTime), 'HH24')) 
 || ':' ||
 to_char(to_date('00', 'MI') + (EndTime - StartTime), 'MI')
 as duration_time

It's not very pretty, I know. Since formatting something like that using an ORM (I'm using NHibernate) is probably a waste of time. I was thinking I could simply allow me DTO to take care of that formatting. I could use something like this in my C# set property.

public TimeSpan DurationTimeSpan
{
 get
 {
  return EndTime.Subtract(StartTime);
 }
}

So my question is, should I let me DTO object take care of such formatting? Or is a DTO object not supposed to be responsible for such thing? Personally, it looks like it might be far cleaner to let me DTO's set properties to do such formatting. From the looks of it, most formatting can probably be achieved with very simple C#.

Answer 1

This definitely sounds like something that should be done well away from the database. The purpose of the database is to store and provide data for your application. Formatting is something which is client-specific - and shouldn't be part of the query, IMO.

Aside from anything else, I suspect you'll find it a lot easier to code/test/debug the formatting in .NET than in SQL :)

Now that doesn't mean putting the logic in your DTOs, necessarily. What if you have two different client views which need to present the same data in different ways? If your DTOs really are just meant to transport the data, they shouldn't worry about how it's presented to the user. That should be in your UI logic. By all means make the DTO convert from the database representation (e.g. "number of seconds") into a more idiomatic .NET type (TimeSpan) but I'd leave formatting to the UI layer.

Answer 2

Well in my point of view the sql should only provide the least amount of formatting that is needed for being able to interpret the data.

The rest of the formatting should realy go into the Data- or even the Business-Layer.