4

I fell upon an integer format for dates for which I also know the date, but I do not know how to get to that date in TSQL and I also do not know how to get to the integer if I have the date:

700444 -> 1918-10-02

731573 -> 2003-12-24

739479 -> 2025-08-16

Those 6-digit numbers would fit as a counter for each day from 0001-01-01 onwards, I checked that by getting the number of days for one century from that date that is almost year 2000 and adding that to 1900:

select DATEADD(dd,731573/20,'19000101')

Out:

2000-02-24 00:00:00.000

But I cannot run select DATEADD(dd,731573/20,'10000101'), which throws:

The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

Microsoft Learn says that TSQL allows dates only from 1753-01-01 onwards, see datetime (Transact-SQL) Microsoft Learn, thus:

select DATEADD(dd,731573/20,'17530101')

Out:

1853-02-24 00:00:00.000

I cannot add the 731573 to year 1, though. Then I found What is the significance of 1/1/1753 in SQL Server?:

--(as said in one of the answers and at Why should you always write "varchar" with the length in brackets behind it? Often, you get the right outcome without doing so - DBA SE, take varchar(length) instead of just varchar)--

SELECT CONVERT(VARCHAR, DATEADD(DAY,-731572,CAST('2003-12-24' AS DATETIME2)),100)

SELECT CONVERT(VARCHAR(30), DATEADD(DAY,-731572,CAST('2003-12-24' AS DATETIME2)),100)

Out:

Jan  1 0001 12:00AM

So that this is proven, the number is the days from the first day of year 0001. Now I wonder whether I can get there without formatting the datetime column as datetime2. My dates are all just in the 20th and 21st century so that I do not need the datetime2. I get the data as datetime and try to avoid a type conversion.

How can I cast this integer in the seven-houndred-thousands as the counter of the days from the year 1 on to a date and how can I get from the date back to that integer without converting the date to datetime2?

3 Answers 3

16

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:

  1. datetime start date, datetimeoffset(7) end date
  2. datetimeoffset(7) start date, datetime end date
  3. both datetime
  4. 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.

0
3

As you say, if you want to remain entitely within the range of DATE (or DATETIME), then you simply offset the integer to rebase it for the appropriate data type.

I just thought I'd talk about some of the background of the date types.

In SQL Server, DATE used 1753-01-01 as day 1, as this was the first full year of the use of the Gregorian calendar in the Anglophone world (Britain and its colonies). It was also a Monday, which is generally regarded as convenient for the weekday cycle.

The actual transition from Julian ("old style") to Gregorian ("new style") occurred in 1752, where Wed October 2 was followed by Thu October 14. There was no disruption in the progression of the weekdays.

The start of the year was also changed to Jan 1 from the start of 1752, whereas in 1751 the year had started on Mar 25/26 (Mar 25 being Lady Day, being one of the traditional "quarter days" of the calendar, and with some ambiguity as to how starts/ends were handled). This meant both 1751 and 1752 had a disrupted length of year, did not contain all months, and March did not fall in a single year before 1752 but spanned two (much as the weekday cycle tends to span multiple years). There are many other irregularities.

1753 is effectively the earliest year in which a date can fall, without there being significant additional complexities to the arithmetic. And it was far enough back, even in the 1980s when the concepts for SQL Server were being formed, to cope with almost all needs for computerised date arithmetic.

The DATETIME2 type is completely "proleptic", so that although it is capable of representing Gregorian dates back to AD 1, Jan 1 (which if memory serves, is AD 1, Jan 3 in Julian), the actual date arithmetic provided by the type is unlikely to be correct for real-world applications, unless ancient dates are already rebased into Gregorian.

The Roman Catholic Church switched to Gregorian in September 1582, and was able to mobilise most of all of the Roman Catholic world to do so, so there are potentially contemporary Gregorian dates as far back as then in the non-Anglophone world.

But by then, Britain had already left the authority of the Pope, under the rule of Henry VIII, and so didn't transition until 170 years later.

Meanwhile, Russia didn't transition until February 1918, which is why the "October Revolution" of 1917 occurred in November according to the Gregorian calendar. Russia still has a very conservative church which follows the Julian calendar, and who therefore celebrate a religious Christmas in what is now January in the Gregorian calendar.

1

The lowest datetime allowed is 1753-01-01. Thus, giving it a try with datetime2 as in the link in the question:

--(as said in one of the answers and at Why should you always write "varchar" with the length in brackets behind it? Often, you get the right outcome without doing so - DBA SE, take varchar(length) instead of just varchar)--

SELECT CONVERT(VARCHAR, DATEADD(DAY,-1,CAST('1753-01-01' AS DATETIME2)),100)

SELECT CONVERT(VARCHAR(30), DATEADD(DAY,-1,CAST('1753-01-01' AS DATETIME2)),100)

Out:

Dec 31 1752 12:00AM

And

SELECT DATEDIFF(dd,'1753-01-01', '2003-12-24')

Gives:

91667

But that does not say anything more to me. Thus, I take datediff from 0 instead, and without even casting it to datetime2, it still works:

SELECT DATEDIFF(dd,'1753-01-01', '0001-01-01')

Out:

-639905

This is the number that just needs to be subtracted from each integer to cast it:

DATEADD(dd, 731573 - 639905 - 1, '17530101')

Makes:

2003-12-24 00:00:00.000

And since I have dates only in the 20th century and later, I can also take another beginning:

SELECT DATEDIFF(dd,'1900-01-01', '0001-01-01')

Out:

-693595

Which would then become:

SELECT DATEADD(dd, 731573 - 693595 - 1, '19000101')

Out:

2003-12-24 00:00:00.000

And then I fell upon this which shows that the casting of datetime takes the 1900-01-01 as the built-in beginning:

select cast(731573 - 693596 as datetime)

Out:

2003-12-24 00:00:00.000

Or you might need:

select convert(varchar(8), cast(731573 - 693596 as datetime), 112)

Out:

20031224

Now to cast that back to the integer, you need:

SELECT DATEDIFF(dd,'1900-01-01', '2003-12-24') + 693596
-- Or:
SELECT DATEDIFF(dd,'1900-01-01', '20031224') + 693596
-- Or:
SELECT DATEDIFF(dd,'19000101', '20031224') + 693596

Out:

731573

Or begin with year 1:

SELECT DATEDIFF(dd,'0001-01-01', '2003-12-24') + 1

Out:

731573

Thus, output for both is the needed integer 731573 in the question's example 2003-12-24.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.