将 Excel 日期编号更改为 Oracle 日期

声明:本页面是StackOverFlow热门问题的中英对照翻译,遵循CC BY-SA 4.0协议,如果您需要使用它,必须同样遵循CC BY-SA许可,注明原文地址和作者信息,同时你必须将它归于原作者(不是我):StackOverFlow 原文地址: http://stackoverflow.com/questions/19398908/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me): StackOverFlow

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-19 02:00:38  来源:igfitidea点击:

Change Excel date number to Oracle date

oracleoracle11goracle10g

提问by Sagar Tippe

I'm having date as 41293in oracle, how can i show it in DD/MON/YYYYformat?

41293在 oracle 中有日期,我如何以DD/MON/YYYY格式显示它?

If i copy pasted it in Excel and change it to date format, it shows 01/19/13Please help me.

如果我将其复制粘贴到 Excel 中并将其更改为日期格式,则会显示01/19/13请帮助我。

回答by Frank Schmitt

The value you have is the number of days since the 30th of December 1899. Try:

您拥有的值是自 1899 年 12 月 30 日以来的天数。尝试:

select to_char(
  to_date('1899-12-30', 'YYYY-MM-DD') + 41293,
  'DD/MON/YYYY') from dual

回答by SriniV

Quoting from Oracle forum:

引自甲骨文论坛

You need a tool to do that, since format is to tell oracle what type of format you have on your date type in the spreadsheet. While you may not have opted to format the date in Excel, it will appear as a date in the previewer. Use the format from this as a guide to enter into the datatype panel.

您需要一个工具来做到这一点,因为 format 是告诉 oracle 您在电子表格中的日期类型的格式类型。虽然您可能没有选择在 Excel 中格式化日期,但它会在预览器中显示为日期。使用此格式作为指南进入数据类型面板。

so, if you have a date that looks like this in the previewer, 19-jan-2006, then your format for the data type panel if you choose to insert that column is going to be DD-MON-YYYY,

因此,如果您在预览器中有一个类似于 2006 年 1 月 19 日的日期,那么如果您选择插入该列,数据类型面板的格式将是 DD-MON-YYYY,

Option 1:

选项1:

Try using the below functions

尝试使用以下功能

FUNCTION FROMEXCELDATETIME ( ACELLVALUE IN VARCHAR2 )
    RETURN TIMESTAMP
IS
    EXCEL_BASE_DATE_TIME CONSTANT TIMESTAMP
            := TO_TIMESTAMP ( '12/31/1899',
                           'mm/dd/yyyy' ) ;

    VAL CONSTANT NUMBER
            := TO_NUMBER ( NULLIF ( TRIM ( ACELLVALUE ),
                                '0' ) ) ;
BEGIN
    RETURN EXCEL_BASE_DATE_TIME
          + NUMTODSINTERVAL ( VAL
                          - CASE
                                WHEN VAL >= 60
                                THEN
                                    1
                                ELSE
                                    0
                            END,
                          'DAY' );
END;

FUNCTION TOEXCELDATETIME ( ATIMESTAMP IN TIMESTAMP )
    RETURN VARCHAR2
IS
    EXCEL_BASE_DATE_TIME CONSTANT TIMESTAMP
            := TO_TIMESTAMP ( '12/31/1899',
                           'mm/dd/yyyy' ) ;

    DIF CONSTANT INTERVAL DAY ( 9 ) TO SECOND ( 9 )
            := ATIMESTAMP
               - EXCEL_BASE_DATE_TIME ;
    DAYS CONSTANT INTEGER := EXTRACT ( DAY FROM DIF );
BEGIN
    RETURN CASE
              WHEN DIF IS NULL
              THEN
                  ''
              ELSE
                  TO_CHAR (  DAYS
                         + CASE
                               WHEN DAYS >= 60
                               THEN
                                   1
                               ELSE
                                   0
                           END
                         + ROUND ( ( EXTRACT ( HOUR FROM DIF )
                                  + ( EXTRACT ( MINUTE FROM DIF )
                                    + EXTRACT ( SECOND FROM DIF )
                                      / 60 )
                                    / 60 )
                                 / 24,
                                 4 ) )
          END;
END;

Option 2:

选项 2:

The excel function would be =TEXT(B2,"MM/DD/YY"), to convert an Excel date value stored in B2. Then try using the test character in Oracle

excel 函数将是=TEXT(B2,"MM/DD/YY"), 转换存储在 B2 中的 Excel 日期值。然后尝试使用Oracle中的测试字符

If considering 1900 Jan 1st as start date,

如果将 1900 年 1 月 1 日视为开始日期,

SELECT
      TO_CHAR ( TO_DATE ( '1900-01-01',
                      'YYYY-MM-DD' )
              + 41293,
              'DD/MON/YYYY' )
FROM
      DUAL

回答by MT0

Microsoft's Documentation

微软的文档

Excel stores dates as sequential serial numbers so that they can be used in calculations. January 1, 1900 is serial number 1, and January 1, 2008 is serial number 39448 because it is 39,447 days after January 1, 1900.

Excel 将日期存储为连续的序列号,以便它们可以用于计算。1900 年 1 月 1 日是序号 1,而 2008 年 1 月 1 日是序号 39448,因为它是 1900 年 1 月 1 日之后的 39,447 天。

Excel has a bugfeature where it considers 1900 to be a leap yearand day 60is 1900-02-29but that day never existed and a correction needs to be applied for this erroneous day.

Excel中有一个错误的功能在其认为1900是一个闰年和日601900-02-29但那天从未存在过,并应用于这一天错误的修正需求。

It does also state that:

它还指出:

Microsoft Excel correctly handles all other leap years, including century years that are not leap years (for example, 2100). Only the year 1900 is incorrectly handled.

Microsoft Excel 正确处理所有其他闰年,包括不是闰年的世纪年(例如,2100)。只有 1900 年处理不当。

Therefore only a single correction is required.

因此只需要一次修正。

So:

所以:

  • Before 1900-03-01you can use DATE '1899-12-31' + value.
  • On or after 1900-03-01you can use DATE '1899-12-30' + value.
  • 1900-03-01您可以使用之前DATE '1899-12-31' + value
  • 在或之后1900-03-01您可以使用DATE '1899-12-30' + value.

Which can be put into a CASEstatement:

可以放在一个CASE语句中:

SELECT CASE
       WHEN value >=  1 AND value < 60
       THEN DATE '1899-12-31' + value
       WHEN value >= 60 AND value < 61
       THEN NULL
       WHEN value >= 61
       THEN DATE '1899-12-30' + value
       END AS converted_date
FROM   your_table