sql - transformar - convierta el número de serie de la fecha de Excel a la fecha regular

funcion inversa fechanumero

Question

Obtuve una columna llamada DateOfBirth en mi archivo csv con la fecha de Excel Número de serie Fecha

Ejemplo:

      36464
      37104
      35412

Cuando formateé celdas en Excel estas se convierten como

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

Necesito hacer esta transformación en SSIS o en SQL. ¿Cómo se puede lograr esto?

Answer 1

Solución SSIS

"El tipo de datos DT_DATE se implementa utilizando un número de coma flotante de 8 bytes. Los días se representan con incrementos de números enteros, comenzando el 30 de diciembre de 1899 y la medianoche como tiempo cero. Los valores de hora se expresan como el valor absoluto de la parte fraccionaria de el número. Sin embargo, un valor de punto flotante no puede representar todos los valores reales, por lo tanto, hay límites en el rango de fechas que se pueden presentar en DT_DATE ". Lee mas

En la descripción anterior, puede ver que puede convertir estos valores implícitamente al DT_DATE a una columna DT_DATE después de convertirlos a un número de punto flotante de 8 bytes DT_R8 .

Utilice una transformación de columna derivada para convertir esta columna en un número de coma flotante de 8 bytes:

(DT_R8)[dateColumn]

Luego DT_DATE a una columna DT_DATE

O lanzarlo dos veces:

(DT_DATE)(DT_R8)[dateColumn]

Puedes ver mi respuesta completa aquí:

• ¿Hay una mejor manera de analizar las fechas de estilo [Integer]. [Integer] en SSIS?

Answer 2

Además de la respuesta de @ Nick.McDermaid, me gustaría publicar esta solución, que convierte no solo el día, sino también las horas, los minutos y los segundos:

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

Por ejemplo

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

Answer 3

En SQL:

select dateadd(d,36464,''1899-12-30'')
-- or thanks to rcdmk
select CAST(36464 - 2 as SmallDateTime)

En SSIS, ver aquí.

http://msdn.microsoft.com/en-us/library/ms141719.aspx

Answer 4

Encontré este tema útil tanto, así que creó un UDF de SQL rápido para él.

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

Answer 5

Esto me funcionó porque a veces el campo era numérico para obtener la porción de tiempo.

Mando:

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

Answer 6

La respuesta marcada no funciona bien, cambie la fecha a "1899-12-30" en lugar de "1899-12-31".

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

Answer 7

Pruebe el enfoque descrito en el siguiente enlace, que implica crear una función que realice la conversión y aplicar la función en su SQL.

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

Answer 8

Puede hacer esto si solo necesita mostrar la fecha en una vista:

CAST será más rápido que CONVERT si tiene una gran cantidad de datos, también recuerde restar (2) de la fecha de excel:

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

Si necesita actualizar la columna para mostrar una fecha, puede actualizarla a través de una combinación (auto-unirse si es necesario) o simplemente intente lo siguiente:

Puede que no tenga que elegir la fecha de Excel como INT, pero como la tabla con la que estaba trabajando era una varchar, primero tenía que hacer esa manipulación. Tampoco quería el elemento "tiempo", así que necesitaba eliminar ese elemento con el lanzamiento final como "fecha".

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

¡Si no está seguro de lo que le gustaría hacer con esta prueba y vuelva a realizar la prueba! Haga una copia de su mesa si lo necesita. ¡Siempre puedes crear una vista!

Answer 9

Puedes convertirlo en un SQL smalldatetime:

cast(41869 - 2 as smalldatetime)

SQL Server cuenta sus fechas desde 01/01/1900 y Excel desde 12/30/1899 = 2 días menos.

Answer 10

Solución Google BigQuery

SQL estándar

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
 )

Answer 11

Tuve que llevar esto al siguiente nivel porque mis fechas de Excel también tenían veces, así que tuve valores como este:

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

(también, para complicarlo un poco más, mi valor numérico con decimal se guardó como NVARCHAR)

El SQL que utilicé para hacer esta conversión es:

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

Answer 12

esto realmente funcionó para mí

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

(menos 1 día más en la fecha)

refiriéndose a la publicación negativa comentada