SSMS 2012: преобразование DATETIME в серийный номер Excel

Question

Кажется, я нигде не могу найти ответ на этот вопрос - я хочу преобразовать дату и время в SQL в серийный номер Excel.

По сути, я ищу функцию DATEVALUE из Excel, но для использования в SQL

Есть идеи, как это сделать? Спасибо

Answer 1

Предполагая, что желаемая дата 2016-05-25

Select DateDiff(DD,'1899-12-30','2016-05-25')

Возвращает

 42515

Если вы хотите часть времени, а также

Declare @Date datetime = '2016-05-25 20:00'
Select DateDiff(DD,'1899-12-30',@Date)+(DateDiff(SS,cast(@Date as Date),@Date)/86400.0)

Возвращает

42515.8333333

Answer 2

Я столкнулся с этой проблемой и нашел, что наиболее элегантным решением будет следующее (как уже упоминалось, добавление +2 необходимо из-за различий между датами SQL и Excel):

Предполагая, что "Столбец" является DateTimeOffset:

      SELECT CAST(CAST(COLUMN_TO_BE_CONVERTED as datetime)+2 as float) as EXCEL_DATE_FLOAT

Если столбец еще не является DateTimeOffset, а является просто DateTime, вам не понадобится двойное приведение; вам просто нужно что-то вроде:

      SELECT CAST(COLUMN_TO_BE_CONVERTED+2 as float) as EXCEL_DATE_FLOAT

Результирующее значение с плавающей запятой в любом случае — это то, что Excel распознает как дату и время, и вы можете легко извлечь оттуда то, что вам нужно. После ручной проверки результатов в Excel я подтвердил, что серийные номера точно соответствуют дате и времени, как я и ожидал.

Answer 3

Вам просто нужно конвертировать ваши datetime значение для int и добавить 1:

SELECT CONVERT(INT,YourDate) + 1
FROM dbo.SomeTable;

Answer 4

С сопоставлением: SQL_Latin1_General_CP1_CI_AS

ты должен использовать

Select DateDiff(DD,'18991230','20160525')

Возвращает 42515

Вы можете заменить '20160525' на getdate() или поле даты