Oracle - 转换8位数字到目前为止 [英] Oracle - Convert number 8 digit number to date

问题描述

我有一个oracle数据库，其中日期记录为8位数，例如：

  59696580，代表'01 / 07/2013 00:00:00'
 59696610代表'01 / 07/2013 00:30:00'
  

有人知道如何将这个8位数字转换成Oracle SQL中的日期？

谢谢

解决方案

这可能取决于时区，但看起来这个数字是自 12-30-1899 1:00:00以来的分钟数，给定您希望他们代表的日期。

为了获取日期，您只需使用添加 + 运算符添加几天即可那个基础时间。鉴于您的数字代表分钟，您可以除以1440，以了解这几分钟代表的天数：

  to_date（'12 -30-1899 1:00:00'，'MM-DD-YYYY HH24：Mi：SS'）+（59696580/1440）
 to_date（'12 -30-1899 1:00:00'，' MM-DD-YYYY HH24：Mi：SS'）+（59696610/1440）
  

SQL Fiddle

I have an oracle database where the dates are recorded as 8 digit number as for example:

59696580 which represent '01/07/2013  00:00:00'
59696610 which represent '01/07/2013  00:30:00'

Would someone know how to convert this 8 digit number into a date in Oracle SQL?

Thanks

解决方案

It might depend on the timezone, but looks like this number is the number of minutes since 12-30-1899 1:00:00, given the dates you want them to represent.

In order to get the date, you can just use the addition + operator to add a number of days to that base time. Given your number represents minutes, you can divide by 1440 to find out how many days do those minutes represent:

to_date('12-30-1899 1:00:00','MM-DD-YYYY HH24:Mi:SS') + (59696580/1440) 
to_date('12-30-1899 1:00:00','MM-DD-YYYY HH24:Mi:SS') + (59696610/1440) 

SQL Fiddle

这篇关于Oracle - 转换8位数字到目前为止的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！