oracle 日期作为sql中的数字
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16445066/
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
Date as number in sql
提问by user2075017
When we type current date in Excel cell as 08-May-2013 Right click on the cell and in the format when i click number as category i get a number
当我们在 Excel 单元格中键入当前日期为 08-May-2013 时右键单击单元格，并在格式中单击数字作为类别我得到一个数字
Date-08-May-13
Formatted one-41402.00
So is there anyway i can get the same number in sql
所以无论如何我可以在sql中获得相同的数字
I tried using this.
我试过用这个。
select to_char(sysdate,'J') from dual
But the output is 2456421
但输出是 2456421
I understand that this is a Julian value
我明白这是一个朱利安值
But can anyone help me in getting the output as that i am getting on excel i.e; 41402
但是任何人都可以帮助我获得输出，因为我正在使用 excel ie；41402
回答by APC
A date in Excel is stored as a serial number, with 01-JAN-1900 as 1. Citation.
Excel 中的日期存储为序列号，其中 01-JAN-1900 为 1。 引用。
We can do arithmetic with dates in Oracle, so converting to an Excel date from Oracle would be:
我们可以在 Oracle 中对日期进行算术运算，因此从 Oracle 转换为 Excel 日期将是：
trunc(sysdate) - to_date( '1900-01-01', 'yyyy-mm-dd')
I've tested this and infuriatingly it produces 41401 - because it's going from midnight. So obviously Microsoft are using a ceiling function to raise it to the next integer:
我已经对此进行了测试，令人气愤的是它产生了 41401 - 因为它是从午夜开始的。所以很明显微软正在使用一个天花板函数将它提升到下一个整数：
ceil (sysdate - to_date( '1900-01-01', 'yyyy-mm-dd') )
回答by Mike Sherrill 'Cat Recall'
The Windows version of Excel stores dates as serial numbers. 01-Jan-1900 is 1, 02-Jan-1900 is 2, etc. The Mac version used to use a different starting date; I don't know whether that's still the case.
Excel 的 Windows 版本将日期存储为序列号。01-Jan-1900 是 1，02-Jan-1900 是 2，以此类推。Mac 版本曾经使用不同的开始日期；我不知道现在是否仍然如此。
The essential data you need is in simple date arithmetic.
您需要的基本数据是简单的日期算术。
select current_date, current_date - date '1900-01-01'
from dual;
That returns 41400.67037037037 for my current connection. Rounding up and adding 1 for fenceposting would return the number you're looking for, but I'd want to test that with multiple time zones and such before I'd swear by it.
这为我当前的连接返回 41400.67037037037。为围栏张贴四舍五入并加 1 将返回您正在寻找的数字，但我想在我发誓之前用多个时区等进行测试。
