Randolph West shares some thoughts on the DATEPART function:
As we learned some time ago, an
INT(integer) in T-SQL uses four bytes, and has a maximum value greater than zero of over 2 billion (there are more than 4 billion values in an integer if we take the negative values into account).Why then are date and time parts expressed as an
INT, which have a lot of overhead for values like 24, 31, and 60?There are two reasons:
Integers make things simpler. When writing arithmetic in T-SQL and other programming languages, the default data type is usually an integer. Having to memorise which data types are returned from built-in functions becomes unnecessary when we know it will be an
INT. Yes, it uses extra memory, but four bytes is a reasonable trade-off against complexity.One of the return values is nanoseconds. Although
DATETIME2(7)only has a granularity down to 100 nanoseconds,DATEPARTallows us to return this value, which requires a data type large enough to contain an integer up to 1 billion (nanoseconds can range from 0 to 999,999,900 in increments of 100).
Randolph also explains what happened a few years back to cause iPhone alarms not to fire on January 1st and 2nd.