I have seen lot of people on the forums asking “How do I format my datetime values in a specific format?”
Well. While you can very well use CONVERT or FORMAT function, you need to think if it is really needed to do the formatting in sql itself.
Here is what happens when you do date formatting in sql
- Formatted Dates become Varchars and wont allow date related calculations (dateadd, datediff,etc)
- They wont allow to make use of index (if defined) if formatted at where clause
- Web page, reports, etc treat them as varchars (calculations, Ordering, etc wont work properly)
So what is the proper way to do this? If you want to show them in a web page, reporting tool, etc let them do the date formatting . All of them support date formatting and it is really easy.
When should you do DATE formatting in sql? You can do it for the following cases
- Export results to text file (csv, etc) with specific date format
- Import data from other sources where dates are in different formats
- Front end application can’t be changed but it needs specific date formats for display purpose
Let me know if your thoughts on this