Número inteiro na casa dos 700.000 como os dias do ano 1: como isso pode ser convertido em tsql para uma data e vice-versa se a data e hora mais antiga for 1753-01-01?

Você determinou corretamente que 693596 é o número de dias entre '1º de janeiro de 0001' e '1º de janeiro de 1900'. Conhecer esse deslocamento permite converter diretamente entre a representação inteira que você recebeu e o datetimetipo do SQL Server.

Dito isto, a pergunta e a resposta mostram um importante mal-entendido.

Agora me pergunto se posso chegar lá sem formatar a coluna datetime como datetime2

Strings não são datas ou horas. Não existe 'formato datetime' ou 'formato datetime2'.

Há vários formatos de string que o SQL Server aceitará para conversão implícita ou explícita em um dos tipos de data ou hora. Essas strings ainda são strings, não tipos de data/hora.

Há também um formato padrão para esses tipos quando eles precisam ser exibidos ou retornados em um formato de string . Uma conversão do tipo interno para uma string é sempre executada quando necessário, mas muitas vezes não é visível para o autor da instrução T-SQL. Também pode ser executado pelo cliente, não pelo SQL Server, dependendo da situação exata.

Adquira o hábito de usar conversões explícitas de tipo de dados com um estilo determinístico ao usar strings para representar datas e horas. Caso contrário, você estará contando com comportamentos legados muitas vezes não documentados que podem levar a resultados inesperados ou erros sutis em seu código.

Por exemplo, quando você escreve:

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

Você está solicitando ao SQL Server que converta implicitamente essas strings em um dos tipos de data/hora disponíveis. Não está documentado qual tipo de dados o SQL Server escolherá e quando.

Claramente, ele não pode escolher datetimea representação de string '0001-01-01' (porque isso resultaria em um erro), embora pudesse escolher '1753-01-01'. No mínimo, ser impreciso causa trabalho extra para o servidor enquanto ele tenta interpretar a string, escolher os tipos de dados de acordo com algum esquema e realizar quaisquer conversões necessárias para que os tipos escolhidos para data de início e data de término sejam comparáveis ​​no DATEDIFFcálculo.

Menciono tudo isso porque a pergunta afirma o desejo de evitar conversões de tipos de dados. Evitar um CASTou (melhor) explícito CONVERTno T-SQL não evita conversões implícitas executadas pelo SQL Server.

Por exemplo, no seu código:

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

O SQL Server precisa converter essas duas strings (possivelmente mais de uma vez) em um tipo de data/hora interno adequado para uso com DATEDIFF. Na maioria das vezes, o SQL Server acabará sendo usado internamente DATETIMEOFFSET(7)nessas circunstâncias.

Acontece que o serviço de expressão interna possui apenas quatro implementações de código para DATEDIFF:

1. datetimedata de início datetimeoffset(7)data de fim
2. datetimeoffset(7)data de início datetimedata de fim
3. ambosdatetime
4. ambosdatetimeoffset(7)

Qualquer outra coisa, incluindo o fornecimento de strings, resultará em conversões.

Não se deve esperar que ninguém se lembre dessas coisas ou dê conta de detalhes internos não documentados como esse, mas isso não significa que você não possa ser afetado por elas.

Escrevendo um de seus exemplos com digitação explícita:

-- Convert from datetime type to integer
DECLARE 
    @Base datetime = CONVERT(datetime, '19000101', 112),
    @ToConvert datetime = CONVERT(datetime, '20031224', 112),
    @Offset integer = 693596;

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;

SELECT Result = DATEADD(DAY, @ToConvert - @Offset, @Base);

Finalmente, a maioria das pessoas prefere usar abreviações explícitas como DAYem vez de DDe é definitivamente recomendado não usar VARCHARsem um comprimento máximo explícito.

Parte disso pode parecer digitação ou verbosidade desnecessária, mas é um conselho que eu gostaria de ter recebido quando comecei a usar o SQL Server. Isso economiza muito mais tempo de depuração futura do que custa ao construir o T-SQL.

Como você disse, se quiser permanecer totalmente dentro do intervalo de DATE (ou DATETIME), basta deslocar o número inteiro para rebaseá-lo para o tipo de dados apropriado.

Eu apenas pensei em falar sobre alguns dos antecedentes dos tipos de data.

No SQL Server, DATE usou 1753-01-01 como dia 1, pois este foi o primeiro ano completo de uso do calendário gregoriano no mundo anglófono (Grã-Bretanha e suas colônias). Era também segunda-feira, o que geralmente é considerado conveniente para o ciclo da semana.

A transição real do Juliano ("estilo antigo") para o Gregoriano ("novo estilo") ocorreu em 1752, onde a quarta-feira, 2 de outubro, foi seguida pela quinta-feira, 14 de outubro.

O início do ano também foi alterado para 1º de janeiro a partir do início de 1752, enquanto em 1751 o ano começou em 25/26 de março (25 de março sendo o Dia da Senhora, sendo um dos tradicionais "quartos de dia" do calendário, e com alguma ambiguidade quanto à forma como os inícios/finais foram tratados). Isso significava que tanto 1751 quanto 1752 tiveram uma duração de ano interrompida, não continham todos os meses e março não caiu em um único ano antes de 1752, mas durou dois (da mesma forma que o ciclo dos dias da semana tende a abranger vários anos). Existem muitas outras irregularidades.

1753 é efetivamente o primeiro ano em que uma data pode cair, sem que haja complexidades adicionais significativas para a aritmética. E já era tempo suficiente, ainda na década de 1980, quando os conceitos do SQL Server estavam sendo formados, para lidar com quase todas as necessidades de aritmética computadorizada de datas.

O tipo DATETIME2 é completamente "proléptico", de modo que, embora seja capaz de representar datas gregorianas de 1 dC, 1º de janeiro (que, se não me falha a memória, é 1 dC, 3 de janeiro em juliano), a aritmética de data real fornecida pelo tipo é improvável que seja correto para aplicações do mundo real, a menos que datas antigas já tenham sido rebaseadas em gregoriano.

A Igreja Católica Romana mudou para o Gregoriano em Setembro de 1582, e foi capaz de mobilizar a maior parte de todo o mundo católico romano para o fazer, pelo que existem datas gregorianas potencialmente contemporâneas já naquela altura no mundo não-anglófono.

Mas nessa altura, a Grã-Bretanha já tinha deixado a autoridade do Papa, sob o governo de Henrique VIII, e por isso só fez a transição 170 anos mais tarde.

Entretanto, a Rússia só fez a transição em Fevereiro de 1918, razão pela qual a “Revolução de Outubro” de 1917 ocorreu em Novembro, de acordo com o calendário gregoriano. A Rússia ainda tem uma igreja muito conservadora que segue o calendário juliano e que, portanto, celebra um Natal religioso no que hoje é janeiro do calendário gregoriano.

A data e hora mais baixa permitida é 1753-01-01. Assim, experimente datetime2 como no link da pergunta:

--(como dito em uma das respostas e em Por que você deve sempre escrever "varchar" com o comprimento entre colchetes atrás dele? Muitas vezes, você obtém o resultado certo sem fazer isso - DBA SE , pegue varchar(length)em vez de apenas varchar)--

SELECIONE 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)

Fora:

Dec 31 1752 12:00AM

E

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

Dá:

91667

Mas isso não me diz mais nada. Assim, eu pego o datediff de 0 e, mesmo sem convertê-lo para datetime2, ele ainda funciona:

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

Fora:

-639905

Este é o número que só precisa ser subtraído de cada número inteiro para convertê-lo:

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

Faz:

2003-12-24 00:00:00.000

E como só tenho datas do século XX e posteriores, também posso tomar outro começo:

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

Fora:

-693595

O que então se tornaria:

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

Fora:

2003-12-24 00:00:00.000

E então me deparei com isso que mostra que a conversão de data e hora leva 1900-01-01 como o início integrado:

select cast(731573 - 693596 as datetime)

Fora:

2003-12-24 00:00:00.000

Ou você pode precisar de:

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

Fora:

20031224

Agora, para converter isso de volta para o número inteiro, você precisa:

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

Fora:

731573

Ou comece com o ano 1:

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

Fora:

731573

Assim, a saída para ambos é o número inteiro necessário 731573no exemplo da pergunta 2003-12-24.