복붙노트

[SQL] 엑셀 날짜 일련 번호 정기적으로 날짜로 변환

SQL

엑셀 날짜 일련 번호 정기적으로 날짜로 변환

나는 엑셀 날짜 일련 번호 날짜 내 csv 파일에 출생의 열이라는 날짜를 얻었다

예:

  36464
  37104
  35412

내가 엑셀에서 셀을 포맷 할 때 다음은 변환됩니다

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

나는 SSIS 또는 SQL이 변환을 할 필요가있다. 이것은 어떻게 달성 할 수 있습니까?

해결법

  1. ==============================

    1.SQL에서 :

    SQL에서 :

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

    SSIS에서 여기를 참조

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

  2. ==============================

    2.표시된 대답은 잘 작동하고, 대신 "1899년 12월 31일"의 "1899-12-30"로 날짜를 변경하시기 바랍니다하지 않습니다.

    표시된 대답은 잘 작동하고, 대신 "1899년 12월 31일"의 "1899-12-30"로 날짜를 변경하시기 바랍니다하지 않습니다.

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

    3.당신은 SQL의 smalldatetime으로로 전송할 수 있습니다 :

    당신은 SQL의 smalldatetime으로로 전송할 수 있습니다 :

    cast(41869 - 2 as smalldatetime)
    

    SQL Server는 01/01/1900에서의 날짜를 계산하고 1899년 12월 30일 = 이일 이하에서 엑셀.

  4. ==============================

    4.이 사실은 나를 위해 일한

    이 사실은 나를 위해 일한

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

    (마이너스 날짜에서 1 개 일)

    부정적인 댓글을 달았 게시물에 참조

  5. ==============================

    5.당신이 8 바이트 부동 소수점 숫자 DT_R8으로 변환 한 후 DT_DATE 컬럼에 매핑 할 때 암시 적으로이 값을 변환 할 수 있음을 알 수 위의 설명에서.

    당신이 8 바이트 부동 소수점 숫자 DT_R8으로 변환 한 후 DT_DATE 컬럼에 매핑 할 때 암시 적으로이 값을 변환 할 수 있음을 알 수 위의 설명에서.

    8 바이트의 부동 소수점 수에이 열 변환 파생 열 변환을 사용하여

    (DT_R8)[dateColumn]
    

    그런 다음 DT_DATE 컬럼에 매핑

    또는 두 번 캐스트 :

    (DT_DATE)(DT_R8)[dateColumn]
    

    당신은 여기 내 전체 답을 확인하실 수 있습니다 :

  6. ==============================

    6.그것을 위해 도움이 많이 생성되므로 빠른 SQL UDF이 주제를 발견.

    그것을 위해 도움이 많이 생성되므로 빠른 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
    
  7. ==============================

    7.내 엑셀 날짜도 시간이 있었기 때문에 나는이 같은 값을 가지고 있으므로, 다음 단계로이을했다 :

    내 엑셀 날짜도 시간이 있었기 때문에 나는이 같은 값을 가지고 있으므로, 다음 단계로이을했다 :

    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. ==============================

    8.20 세기의 시작 이후 경과 된 시간을 나타내며, SQL Server를 쉽게 같은 방식으로 수레와 날짜 시간 사이에 캐스트 할 수있는 부동 소수점 숫자로 엑셀 저장 날짜 시간. Excel 및 날짜 시간이 수의 SQL 서버의 전환의 차이는 (즉, 1900년 3월 1일의 등) 2 일입니다. 암시 적으로 매우 입력 친절하고 간단한 쿼리에 대한 수레에 다른 데이터 유형 변환이 차이 정보 용 SQL Server에 대한 2E의 문자를 사용 :

    20 세기의 시작 이후 경과 된 시간을 나타내며, SQL Server를 쉽게 같은 방식으로 수레와 날짜 시간 사이에 캐스트 할 수있는 부동 소수점 숫자로 엑셀 저장 날짜 시간. Excel 및 날짜 시간이 수의 SQL 서버의 전환의 차이는 (즉, 1900년 3월 1일의 등) 2 일입니다. 암시 적으로 매우 입력 친절하고 간단한 쿼리에 대한 수레에 다른 데이터 유형 변환이 차이 정보 용 SQL Server에 대한 2E의 문자를 사용 :

    select
        cast('43861.875433912' - 2e as datetime) as ExcelToSql, -- even varchar works!
        cast(cast('2020-01-31 21:00:37.490' as datetime) + 2e as float) as SqlToExcel
    
    -- Results:
    -- ExcelToSql                          SqlToExcel
    -- 2020-01-31 21:00:37.490        43861.875433912
    
  9. ==============================

    9.@ Nick.McDermaid의 또 내가이 솔루션, 변환 일뿐 아니라 시간, 분, 초를 게시하고 싶습니다 답변 :

    @ Nick.McDermaid의 또 내가이 솔루션, 변환 일뿐 아니라 시간, 분, 초를 게시하고 싶습니다 답변 :

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

    예를 들면

  10. ==============================

    10.당신은 단지보기에서 날짜를 표시해야하는 경우이 작업을 수행 할 수 있습니다 :

    당신은 단지보기에서 날짜를 표시해야하는 경우이 작업을 수행 할 수 있습니다 :

    당신은 또한, 많은 양의 데이터를 엑셀 날짜로부터 (2)를 뺄 기억한다면 CAST는 빠른 CONVERT보다 더 될 것입니다 :

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

    당신이 통해서 업데이트가 가입 할 수있는 날짜를 표시하기 위해 열을 업데이트해야하는 경우 (자기가 필요한 경우에 가입)하거나 다음을 시도하십시오

    당신은 INT와 엑셀 날짜를 캐스팅 할 필요가 없습니다 수 있지만 테이블 때문에 내가 먼저 조작을 할 수 있었던 VARCHAR이었다 작업을했다. 나뿐만 최종 캐스트와 함께 해당 요소를 제거하기 위해 필요한, 그래서 나는 또한 "시간"요소를 원하지 않았다 "날짜입니다."

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

    당신이 무엇을 모르는 경우이 테스트 및 다시 테스트와 함께 할 싶습니다! 당신이 필요로하는 경우 테이블의 복사본을 만듭니다. 당신은 항상 뷰를 만들 수 있습니다!

  11. ==============================

    11.구글의 BigQuery 솔루션

    구글의 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
     )
    
  12. ==============================

    12.PostgreSQL의에서 다음과 같은 구문을 사용할 수 있습니다 :

    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년 9월 12일 18시 30분 30초를 나타낸다

  13. ==============================

    13.converstion 및 수행하여 SQL에서 함수를 적용하는 기능을 만드는 작업이 포함됩니다 다음 링크에서 논의 된 접근 방식을 사용해보십시오.

    converstion 및 수행하여 SQL에서 함수를 적용하는 기능을 만드는 작업이 포함됩니다 다음 링크에서 논의 된 접근 방식을 사용해보십시오.

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

  14. ==============================

    14.때로는 필드는 시간 부분을 얻을 숫자 때문에이 나를 ​​위해 일했다.

    때로는 필드는 시간 부분을 얻을 숫자 때문에이 나를 ​​위해 일했다.

    명령:

     dateadd(mi,CONVERT(numeric(17,5),41869.166666666664)*1440,'1899-12-31') 
    
  15. from https://stackoverflow.com/questions/13850605/convert-excel-date-serial-number-to-regular-date by cc-by-sa and MIT license