Is DATE formatting in SQL good?

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

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s