You have correctly determined that 693596 is the number of days between '1 January 0001' and '1 January 1900'. Knowing this offset allows you to convert directly between the integer representation you have been given and the SQL Server datetime type.
That said, the question and answer show an important misunderstanding.
Now I wonder whether I can get there without formatting the datetime column as datetime2
Strings are not dates or times. There is no such thing as 'datetime format' or 'datetime2 format'.
There are a variety of string formats which SQL Server will accept for implicit or explicit conversion into one of the date or time types. These strings are still strings, not date/time types.
There is also a default format for these types when they are required to be displayed or otherwise returned in a string format. A conversion from the internal type to a string is always performed when this is necessary, but it is often not visible to the T-SQL statement author. It may also be performed by the client, not SQL Server, depending on the exact situation.
Get into the habit of using explicit data type conversions with a deterministic style when using strings to represent dates and times. Otherwise, you're relying on often undocumented legacy behaviours that can lead to unexpected outcomes or subtle bugs in your code.
For example, when you write:
SELECT DATEDIFF(dd,'1753-01-01', '0001-01-01')
You are asking SQL Server to implicitly convert those strings to one of the available date/time types. It's not documented which data type SQL Server will choose and when.
Clearly, it can't choose datetime for the string representation '0001-01-01' (because that would result in an error) though it might for '1753-01-01'. At the very least, being imprecise causes extra work for the server as it attempts to interpret the string, choose data types according to some scheme, and perform any necessary conversions so the chosen types for start date and end date are comparable in the DATEDIFF computation.
I mention all this because the question states the desire to avoid data type conversions. Avoiding an explicit CAST or (better) CONVERT in T-SQL does not avoid implicit conversions performed by SQL Server.
For example, in your code:
SELECT DATEDIFF(dd,'0001-01-01', '2003-12-24') + 1
SQL Server has to convert both of those strings (possibly more than once) to a suitable internal date/time type suitable for use with DATEDIFF. Much of the time, SQL Server will internally end up using DATETIMEOFFSET(7) in these circumstances.
As it happens, the internal expression service only has four code implementations for DATEDIFF:
datetime start date, datetimeoffset(7) end datedatetimeoffset(7) start date, datetime end date- both
datetime
- both
datetimeoffset(7)
Anything else, including providing strings, will result in conversions.
Nobody should be expected to remember these things, or account for undocumented internal details like this, but that doesn't mean you can't be affected by them.
Writing one of your examples with explicit typing:
-- Convert from datetime type to integer
DECLARE
@Base datetime = CONVERT(datetime, '19000101', 112),
@ToConvert datetime = CONVERT(datetime, '20031224', 112),
@Offset integer = 693596;
-- Returns the required 731573
SELECT Result = DATEDIFF(DAY, @Base, @ToConvert) + @Offset;
GO
-- Convert from integer to datetime type
DECLARE
@Base datetime = CONVERT(datetime, '19000101', 112),
@ToConvert integer = 731573,
@Offset integer = 693596;
-- Returns the required 2003-12-24
SELECT Result = DATEADD(DAY, @ToConvert - @Offset, @Base);
Online db<>fiddle demo
Finally, most people prefer to use explicit abbreviations like DAY instead of DD and it is definitely recommended not to use VARCHAR without an explicit maximum length.
Some of this might seem like unnecessary typing or verbosity, but it is advice I wish I were given when I started out with SQL Server. It saves much more future debugging time than it costs when constructing the T-SQL.
