Конвертировать Excel Date Serial Number в стандартную дату

У меня есть столбец с именем DateOfBirth в моем файле csv с датой даты в Excel Date

Пример:

      36464
      37104
      35412

Когда я форматировал ячейки в excel, они преобразуются как

      36464 => 1/11/1999
      37104 => 1/08/2001
      35412 => 13/12/1996

Мне нужно сделать это преобразование в SSIS или SQL. Как это можно достичь?

Ответы

Ответ 2

Отмеченный ответ не работает нормально, пожалуйста, измените дату на "1899-12-30" вместо "1899-12-31".

select dateadd(d,36464,'1899-12-30')

Ответ 3

Вы можете отправить его в smalldatetime SQL:

cast(41869 - 2 as smalldatetime)

SQL Server подсчитывает свои даты с 01/01/1900 и Excel с 12/30/1899 = на 2 дня меньше.

Ответ 4

это действительно сработало для меня

dateadd(mi,CONVERT(numeric(17,5),41869.166666666664)*1440,'1899-12-30')

(минус еще 1 день в день)

ссылаясь на сообщение с отрицательным комментарием

Ответ 5

Этот раздел полезен, поэтому создал быстрый SQL UDF для него.

CREATE FUNCTION dbo.ConvertExcelSerialDateToSQL
(
    @serial INT
)
RETURNS DATETIME
AS
BEGIN
    DECLARE @dt AS DATETIME
    SELECT @dt = 
        CASE
            WHEN @serial is not null THEN CAST(@serial - 2 AS DATETIME)
            ELSE NULL
        END
    RETURN @dt              
END
GO

Ответ 6

Решение SSIS

"Тип данных DT_DATE реализуется с использованием 8-байтового числа с плавающей запятой. Дни представлены с шагом в целые числа, начиная с 30 декабря 1899 года и в полночь в качестве нуля 0. Часовые значения выражаются как абсолютное значение дробная часть числа. Однако значение с плавающей запятой не может представлять все реальные значения, поэтому существуют ограничения на диапазон дат, которые могут быть представлены в DT_DATE". Подробнее

Из приведенного выше описания вы можете видеть, что вы можете неявно преобразовывать эти значения при сопоставлении их с столбцом DT_DATE после преобразования его в число с плавающей запятой с 8 байтами DT_R8.

Используйте преобразование производного столбца, чтобы преобразовать этот столбец в 8-байтовый номер с плавающей запятой:

(DT_R8)[dateColumn]

Затем сопоставьте его с столбцом DT_DATE

Или выполните его дважды:

(DT_DATE)(DT_R8)[dateColumn]

Вы можете проверить мой полный ответ здесь:

Ответ 7

Мне пришлось перейти на следующий уровень, потому что мои даты в Excel также имели время, поэтому у меня были такие значения:

42039.46406 --> 02/04/2015 11:08 AM
42002.37709 --> 12/29/2014 09:03 AM
42032.61869 --> 01/28/2015 02:50 PM

(также, чтобы усложнить это немного, мое числовое значение с десятичным числом было сохранено как NVARCHAR)

SQL, который я использовал для этого преобразования:

SELECT DATEADD(SECOND, (
                        CONVERT(FLOAT, t.ColumnName) - 
                        FLOOR(CONVERT(FLOAT, t.ColumnName))
                       ) * 86400,
               DATEADD(DAY, CONVERT(FLOAT, t.ColumnName), '1899-12-30')
              )

Ответ 8

Кроме ответа @Nick.McDermaid, я хотел бы опубликовать это решение, которое конвертирует не только день, но также часы, минуты и секунды:

SELECT DATEADD(s, (42948.123 - FLOOR(42948.123))*3600*24, dateadd(d, FLOOR(42948.123),'1899-12-30'))

Например

  • 42948.123 to 2017-08-01 02:57:07.000
  • 42818.7166666667 to 2017-03-24 17:12:00.000

Ответ 9

Вы можете сделать это, если вам просто нужно отобразить дату в представлении:

CAST будет быстрее, чем CONVERT, если у вас большой объем данных, также помните, чтобы вычесть (2) из ​​даты excel:

CAST(CAST(CAST([Column_With_Date]-2 AS INT)AS smalldatetime) AS DATE)

Если вам нужно обновить столбец, чтобы показать дату, вы можете либо обновить его через соединение (при необходимости присоединитесь), либо попробуйте следующее:

Возможно, вам не нужно указывать дату excel как INT, но поскольку таблица, с которой я работал, была varchar, мне пришлось сначала сделать эту манипуляцию. Мне также не нужен элемент "time", поэтому мне нужно было удалить этот элемент с окончательным приложением как "дата".

UPDATE [Table_with_Date]
SET [Column_With_Excel_Date] = CAST(CAST(CAST([Column_With_Excel_Date]-2 AS INT)AS smalldatetime) AS DATE)

Если вы не знаете, что вы хотели бы сделать с этим тестом и повторным испытанием! При необходимости сделайте копию своей таблицы. Вы всегда можете создать представление!

Ответ 10

Решение Google BigQuery

Стандартный SQL

Select Date, DATETIME_ADD(DATETIME(xy, xm, xd, 0, 0, 0),  INTERVAL xonlyseconds SECOND) xaxsa
from (
  Select Date, EXTRACT(YEAR FROM xonlydate) xy, EXTRACT(MONTH FROM xonlydate) xm, EXTRACT(DAY FROM xonlydate) xd, xonlyseconds
  From (
     Select Date
        , DATE_ADD(DATE '1899-12-30', INTERVAL cast(FLOOR(cast(Date as FLOAT64)) as INT64) DAY )   xonlydate
        , cast(FLOOR( ( cast(Date as FLOAT64) - cast(FLOOR( cast(Date as FLOAT64)) as INT64)  ) * 86400 ) as INT64) xonlyseconds
     FROM (Select '43168.682974537034' Date) -- 09.03.2018  16:23:28
   ) xx1
 )

Ответ 11

В postgresql вы можете использовать следующий синтаксис:

SELECT ((DATE('1899-12-30') + INTERVAL '1 day' * FLOOR(38242.7711805556)) + (INTERVAL '1 sec' * (38242.7711805556 - FLOOR(38242.7711805556)) * 3600 * 24)) as date

В этом случае 38242.7711805556 представляет 2004-09-12 18:30:30 в формате Excel

Ответ 12

Попробуйте подход, описанный в следующей ссылке, который включает в себя создание функции, которая выполняет преобразование и применение функции в вашем SQL.

http://www.ehow.com/how_12073756_convert-julian-date-sql.html

Ответ 13

Это сработало для меня, потому что иногда поле было числовым, чтобы получить временную часть.

Команда:

 dateadd(mi,CONVERT(numeric(17,5),41869.166666666664)*1440,'1899-12-31')