Does Date Have a Format?

We all know about datatypes in working with data, as we have to deal with data every day. There are two very innocent datatypes called String and Date. However, when these two datatypes talk to each other, sometimes some of us get confused. Let us first check below dialogues:

Client: What’s the column name and datatype? 

Developer: It’s LoadDate. Datatype is Date.

Client: Oh, it’s a date, And what’s it format again? 

Developer: I just told you it’s a Date type. It doesn’t have a format. 

Client: Are you sure? Date always got a format. 

 Or take another one:

App Developer: I have written a procedure. Call it from your ETL job. It takes a Date as input. 

ETL Developer: Sure I will. 

App Developer: And make sure format is MMDDYYYY.

ETL Developer: I just checked the procedure. The parameter datatype is Date so the format doesn’t matter.

App Developer: Are you sure? Date always got a format. 

If you spent some good amount of years into IT, I am sure you also have come across similar situation or were right or wrong side of those above conversation. Now where is the confusion here. It’s a very simple concept and still some of us gets confused with this: whether or not Date has a format. Those who think Date has a format, think so because otherwise how should a date value be expressed with. Right? It’s got to be either DDMMYYYY or YYYY/DD/MM or DD-Mon-YY or something. Very simple! Apparently. Now let me tell you where my friends in above two examples got it wrong. 

A Date is simply a date and it doesn’t need any format. However, when we convert it to string it needs a format. Like wise, when a string needs to convert into Date it needs a format. Format is needed for datatype conversation and not for any other Date operation. 

It’s a like water. Water doesn’t have any shape. Only when you keep it in glass or bottle, it takes the shape of glass or bottle. Remember this analogy.

When you select date field in SSMS or SQL Developer or any tool for that matter, it displays with a specific format:

But that’s because the SSMS or SQL Developer tools has those default format setting for Date fields to display the date values. That’s nothing to do with Table schema or Date field. 

Those tools accept specific format string when you write a query using Date fields. Like in below example, we created a table which has a Date column. Note that when we inserted rows into table we used format ‘MM/DD/YYYY’, when we query the same table we used literals with format ‘DD-MON-YYYY’ and the results are displayed in format ‘YYYY-MM-DD’.

This proves that Date (datatype) doesn’t have any format. When we forget to mention conversion function like TO_DATE, it’s the query tool which has its default format setting to interact with users and apply its intelligence to convert those strings into dates. If you try this inside a procedure or program, it will fail as there it wont have that intelligence. But the point here is, the data that resides in the table is - Third day of June of year 2022, irrespective of the format.

When you check the log files, there also date values shown with some format, which again are tool or application specific settings, because Date has to be converted to a string value first before it can be printed into log files. 

Whatever we discussed for Date hold true Datetime or Timestamp datatypes as well. 

Hope this clarifies the doubt. Again it’s very simple concept but I have seen sometimes very experienced professionals get confused with this. The datatype Date doesn’t have format, the string which expresses its value, has a format.