Is Int 1 always implicitly converted to 1 day?

Question

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?

Answer 1

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.

Answer 2

The answer to your question is yes. But don't rely on it. You can also add 6 hours with +0.25, because thats how the internal representation is in SQL Server. It looks like a lot of documentation is cleaned up, but I have seen old documents specifying the internal representation.

select cast(cast('31 dec 1899' as datetime) as float)
select cast(cast('1 jan 1900' as datetime) as float)
select cast(1.25 as datetime)
select cast(cast('2 jan 1900' as datetime) as float)

Many years ago I remember misusing this by casting a lot of dates to float, doing average and casting back to get the average date. But I don't think I would do that today.

Answer 3

One handy tool for exploring data type issues is SQL_VARIANT_PROPERTY:

select sql_variant_property( GetDate(), 'BaseType' ); -- datetime.
select sql_variant_property( 1, 'BaseType' ); -- int.

If you combine the two in an expression:

select sql_variant_property( GetDate() + 1, 'BaseType' ); -- datetime.

As you have found, the rules for data type precedence and data type conversion show that an implicit conversion will occur from int to datetime in the previous expression. An explicit conversion to the target data type should provide the same result:

select sql_variant_property( Cast( 1 as DateTime ), 'BaseType' ); -- datetime.
select Cast( 1 as DateTime ); -- 1900-01-02 00:00:00.000.

In a delightfully circular way this is documented as:

Expression Results:

When two expressions are combined by using arithmetic, bitwise, or string operators, the operator determines the resulting data type.

+ (Addition):

Returns the data type of the argument with the higher precedence. For more information, see Data Type Precedence (Transact-SQL).

Data type precedence: The table shows datetime (6) is a higher precedence than int (16).

Which leaves you back at Data type conversion.

That ought to mean that the code samples show the behavior to be expected and that 1 will be implicitly converted to one day as a datetime. Explicit documentation is not always available.

Aside: Another useful place to check is Constants. It is silent on the issue at hand.

Answer 4

CURRENT_TIMESTAMP is the ANSI compatible function and returns the same value as the SQL Server function GETDATE() both of which derive from the operating system from the computer on which they run. CURRENT_TIMESTAMP works with other ANSI compatible databases whereas GETDATE() does not.

Unless I need a UTC date I use the CURRENT_TIMESTAMP form since it really IS a timestamp with date/time - unless it is assigned for example: Both these work but are just the Date type not DateTime

DECLARE @MYDATE DATE = GETDATE();
DECLARE @MYDATE DATE = CURRENT_TIMESTAMP;

It is not just an integer as these all work in SQL Server.

SELECT
    CURRENT_TIMESTAMP,
    CURRENT_TIMESTAMP + 1,
    CURRENT_TIMESTAMP - 1,
    CURRENT_TIMESTAMP + 1.5,
    CURRENT_TIMESTAMP - 1.5,
    GETDATE(),
    GETDATE() + 1,
    GETDATE() + 1.5,
    GETUTCDATE(),
    GETUTCDATE() -5,
    DATEADD(DAY, 1, CURRENT_TIMESTAMP),
    DATEADD(DAY, -1, CURRENT_TIMESTAMP),
    DATEADD(DAY, -1.5, CURRENT_TIMESTAMP)
    ;

I always recommend the use of the DATEADD(DAY, 1, CURRENT_TIMESTAMP) for clarity of purpose and as this also makes it simpler when you also need hours DATEADD(HOUR, 8, CURRENT_TIMESTAMP) DATEADD(MINUTE, 1400, CURRENT_TIMESTAMP) or other datetime adjustments;