Manipulate data in the DB query or in the code

Question

How do you decide on which side you perform your data manipulation when you can either do it in the code or in the query ?

When you need to display a date in a specific format for example. Do you retrieve the desired format directly in the sql query or you retrieve the date then format it through the code ?

What helps you to decide : performance, best practice, preference in SQL vs the code language, complexity of the task... ?

Answer 1

All things being equal I prefer to do any manipulation in code. I try to return data as raw as possible so its usuable by a larger base of consumers. If its very specialized, maybe a report, then I may do manipulation on the SQL side.

Another instance where I prefer to do manipulation on the SQL side is if it can be done set based.

If its not set based, and looping would be involved, then I would do the manipulation in code.

Basically let the database do what its good at, otherwise do it in code.

Answer 2

Formatting is a UI issue, it is not 'manipulation'.