sql - 如何将 OLE 自动化日期值转换为 SQL Server 中的日期

Question

我的应用程序将日期存储为 OLE 自动化使用 DateTime.ToOADate() 命令加倍的日期。

我需要创建一个 SQL View 来显示存储的日期。如何快速将 double 转换为日期？

Answer 1

有

SELECT CAST(CASE WHEN OLEFLOAT > 0 THEN 
                         OLEFLOAT-2.0 
                 ELSE 
       2*CAST(OLEFLOAT AS INT) - 2.0 +  ABS(OLEFLOAT) END as datetime)

工作？来自 here

An OLE Automation date is implemented as a floating-point number whose integral component is the number of days before or after midnight, 30 December 1899, and whose fractional component represents the time on that day divided by 24. For example, midnight, 31 December 1899 is represented by 1.0; 6 A.M., 1 January 1900 is represented by 2.25; midnight, 29 December 1899 is represented by -1.0; and 6 A.M., 29 December 1899 is represented by -1.25.

这听起来很像 SQL Server 在将日期转换为 float 时使用的相同系统，除了偏移量需要调整 2 和“负”日期。 SQL Server 将向后减去。因此 -1.25 是 18:00，而对于 OLE 则意味着 06:00。