Search code examples
sql-servert-sqltypesimplicit-conversion

Is Int 1 always implicitly converted to 1 day?


In SQL Server there are rules for implicitly converting data types.

On this documentation page there is a table about what conversions are possible.

On this documentation page we can see what gets converted to what.

When I implicitly convert an integer to datetime the interval implicitly assumed is 1 day. Therefore the following returns the same time tomorrow:

select getdate() + 1

However, one could argue that 1 should be an hour or a minute. I tried to find in the documentation where it is specified that the interval for implicit conversion to datetime is always 24 hours. Right at the beginning of the documentation of datetime it says:

Defines a date that is combined with a time of day with fractional seconds that is based on a 24-hour clock.

But I don't feel this to be precise and clear enough to convince somebody that the integer 1 will unchangeably represent 1 day if implicitly converted.

So is there a place where Microsoft makes this clear?


Solution

  • Is Int 1 always implicitly converted to 1 day?"*

    TL;DR: No. It is for (small)datetime, but for modern data types it will generate an error.


    As for why it's 1 for a day most likely it's something that predates SQL Server and likely comes from Sybase (though I have no way of confirming this). Why 1 was chosen as a day, I don't know. Maybe it's because another application at the time did something similar; Excel does the same, for example, but their numbers don't align (0 is 1900-01-00 and 1 is 1900-01-01, however, it later drifts more due treating 1900 as having a leap year, so today is 45371 in SQL Server, but 45373 in Excel). If it wasn't because of some other application, it was likely arbitrary.

    Like you mention in the comments, code like GETDATE() + 1 could easily be seen as ambiguous; what is 1 as a datetime? A user could easily be forgiven for expecting it to be something else (such as a second like with epochs).

    This behaviour is not present in the modern date and time data types: date, datetime2, datetimeoffset, and time. The data types do not allow conversions from a numerical value to them, explicit or implicit, or vice versa. If you tried to add 1 to such a date you would get an error:

    SELECT SYSDATETIME() + 1;
    

    Operand type clash: datetime2 is incompatible with int

    In truth, the best thing to do is be explicit. There is a function for adding/subtracting time periods to a date (and time) value: DATEADD. The function works for all the date and time data types ("new" and "old"), and the syntax is very explicit:

    SELECT DATEADD(DAY, 1, SYSDATETIME());
    

    No one, now, can be forgiven for thinking the above is adding anything other than 1 day to the current value of system date and time.