Konvertieren Sie die Excel-Seriennummer in ein reguläres Datum

Habe ich eine Spalte namens " DateOfBirth in meiner csv-Datei mit Excel-Datum-Seriennummer-Datum

Beispiel:

      36464
      37104
      35412

Wenn ich die formatierte Zellen in excel diese konvertiert, als

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

Brauche ich, um diese transformation in SSIS oder SQL. Wie kann das erreicht werden?

InformationsquelleAutor der Frage Sreedhar | 2012-12-12

26

In SQL:
```
select dateadd(d,36464,'1899-12-30')
-- or thanks to rcdmk
select CAST(36464 - 2 as SmallDateTime)
```
In SSIS finden Sie hier

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

InformationsquelleAutor der Antwort Nick.McDermaid
9

Die markierte Antwort ist nicht fein arbeiten, bitte ändern Sie das Datum "1899-12-30" statt "1899-12-31".
```
select dateadd(d,36464,'1899-12-30')
```
InformationsquelleAutor der Antwort Jacob Santiago
7

Können Sie werfen es auf einen SQL smalldatetime:
```
cast(41869 - 2 as smalldatetime)
```
SQL Server zählt seine Termine aus 01/01/1900 und Excel aus 12/30/1899 zurück = 2 Tage weniger.

InformationsquelleAutor der Antwort rcdmk
4

dieser tatsächlich für mich gearbeitet
```
dateadd(mi,CONVERT(numeric(17,5),41869.166666666664)*1440,'1899-12-30') 
```
(minus 1 Tag im Datum)

verweisen auf die negativen kommentierte post

InformationsquelleAutor der Antwort drinky
1

SSIS-Lösung

"Den DT_DATE-Datentyp implementiert ist, mit einem 8-byte-Gleitkommazahl. Die Tage sind repräsentiert durch die ganze Zahl Schritten, beginnend mit 30. Dezember 1899, und Mitternacht, als die Zeit null. Stunde Werte sind ausgedrückt als der absolute Wert der gebrochene Teil der Zahl. Jedoch, eine floating-point-Wert kann nicht repräsentieren alle real-Werte; daher gibt es Grenzen für den Bereich der Daten, die dargestellt werden können, in DT_DATE." Lesen Sie mehr

Aus der obigen Beschreibung kann man sehen, dass können Sie konvertieren Sie diese Werte implizit beim zuordnen zu einer DT_DATE Spalte nach der Konvertierung in ein 8-byte-Gleitkommazahl DT_R8.

Verwenden Sie eine transformation für abgeleitete Spalten konvertieren diese Spalte 8-byte-floating-point-Zahl:
```
(DT_R8)[dateColumn]
```
Ordnen Sie es ein DT_DATE Spalte

Gegossen oder es zweimal:
```
(DT_DATE)(DT_R8)[dateColumn]
```
Können Sie überprüfen, meine ausführliche Antwort hier:
- Gibt es eine bessere Art und Weise zu analysieren [Integer].[Integer] - style Termine im SSIS?
InformationsquelleAutor der Antwort Hadi

Fand dieses Thema hilfreich, so viel, so erstellt eine schnelle 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

InformationsquelleAutor der Antwort Lankymart

0

Zusätzlich von @Nick.McDermaid beantworten, ich möchte nach dieser Lösung, welche die Umwandlung von nicht nur die Tage sondern auch die Stunden, Minuten und Sekunden:
```
SELECT DATEADD(s, (42948.123 - FLOOR(42948.123))*3600*24, dateadd(d, FLOOR(42948.123),'1899-12-30'))
```
Beispielsweise
- 42948.123 zu 2017-08-01 02:57:07.000
- 42818.7166666667 zu 2017-03-24 17:12:00.000
InformationsquelleAutor der Antwort Nicolaesse
0

Können Sie dies tun, wenn Sie gerade brauchen, um das Datum im Blick:

CAST wird schneller sein als CONVERT wenn Sie eine große Menge von Daten, denken Sie auch daran, um zu subtrahieren (2) aus der excel-Datum:
```
CAST(CAST(CAST([Column_With_Date]-2 AS INT)AS smalldatetime) AS DATE)
```
Wenn Sie müssen, aktualisieren Sie die Spalte, um zu zeigen, ein Datum können Sie entweder update über einen join (self-Joins wenn nötig) oder einfach versuchen Sie Folgendes:

Können Sie nicht brauchen, um Darsteller die excel-Datum als INT, aber da der Tisch, an dem ich arbeitete, war eine varchar-ich hatte zu tun, dass die manipulation zuerst. Ich wollte auch nicht die "Zeit" - element, so musste ich entfernen, das element mit der endgültigen Besetzung als "Datum".
```
UPDATE [Table_with_Date]
SET [Column_With_Excel_Date] = CAST(CAST(CAST([Column_With_Excel_Date]-2 AS INT)AS smalldatetime) AS DATE)
```
Wenn Sie unsicher sind, was Sie tun wollen, mit diesem test und re-test! Machen Sie eine Kopie Ihrer Tabelle, wenn Sie brauchen. Sie können immer einen Blick!

InformationsquelleAutor der Antwort Turkish

Musste ich dies auf die nächste Stufe, weil meine Excel-Daten hatten die auch mal, also ich hatte Werte wie diese:

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

(auch, erschweren es ein wenig mehr, meine numerischer Wert mit Dezimalstellen gespeichert wurde, als NVARCHAR)

SQL, die ich verwendet, um diese Umwandlung ist:

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

InformationsquelleAutor der Antwort hurleystylee

-2

Versuchen den Ansatz diskutiert in dem folgenden link, die umfasst das erstellen einer Funktion führt die Konvertierung und Anwendung der Funktion in der SQL.

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

InformationsquelleAutor der Antwort
-3

Dieser arbeitete für mich, weil manchmal war das Feld eine numerische, um die Zeit Teil.

Befehl:
```
 dateadd(mi,CONVERT(numeric(17,5),41869.166666666664)*1440,'1899-12-31') 
```
InformationsquelleAutor der Antwort user2788991

Schreibe einen Kommentar

Du musst angemeldet sein, um einen Kommentar abzugeben.

SSIS-Lösung