(This is a shortened version of my most recent blog post)
This is probably one of the most frequent questions I see on stackoverflow:
How to change the date/datetime/time format.
This question comes all the time in the tags I’m following (c#, SQL Server, SQL etc’) and I’ll bet it comes frequently in any other tag as well.
I want to be as clear as I can about this: Date and time values have no concept of “format”. They are binary values, usually representing the number of time units since a specific epoch.
In C#, A DateTime value is the number of ticks (= 100 nanosecond) since Midnight, January 1st 0001 A.D in the Gregorian calendar, .
In Sql Server, different date and time data types are stored differently - but they are still basically stored as the integer value of a time unit since an epoch (datetime use 4 bytes for days since January 1st 1900 and 4 more bytes for the number of ticks (= 1/300 second) since Midnight - while DateTime2 has a far more sophisticated storage format to allow far grater accuracy and range) - but they are all basically working on the same principle - Store the number of units since an epoch.
Neither of these storage systems has the capability or need to store the display format of a DateTime value, and I’m guessing that in other technologies datetime values are probably stored in a similar manner.
Therefore, the concept of “format” is only relevant to date and time values when they are being displayed to us humans – as strings.