英文:
Date in INT but it's not epoch time
问题
我在客户的SQL Server数据库中声明了'77337'和'4477877'为INT类型。他们的应用程序显示以下日期和时间：分别为2012年9月24日和12:26。这些是如何计算的？
来自数据库：
来自应用程序：
我尝试使用纪元时间或Unix时间，但它不太匹配。
已编辑截图以提供更多示例。
英文:
I have '77337' and '4477877' delcared as INT in a client's database in SQL Server.
Their app displays the following date and time as 9/24/2012 and 12:26 respectively.
How were these computed?
From the database:
From the application:
I tried use epoch time or unix time, but it's a far match
Edited the screenshots for more examples
答案1
得分: 0
如@zhorov在评论中提到的，entrydate的值是距离1800-12-28之后的天数，因此完整的计算可以是：
select *, CONVERT(date, (DATEADD(day, entrydate, '1800-12-28'))) AS DATE,
FLOOR(entrytime/3600/100) AS HOURS ,
FLOOR(entrytime/3600%100*60/100) AS MINUTES
from mytable
结果：
| entrydate | entrytime | DATE | HOURS | MINUTES |
|---|---|---|---|---|
| 77337 | 4477877 | 2012-09-24 | 12 | 25 |
| 77418 | 3135704 | 2012-12-14 | 8 | 42 |
英文:
As mentioned by @zhorov in comments the entrydate values are number of days after 1800-12-28, so the complete calcul can be :
select *, CONVERT(date, (DATEADD(day, entrydate, '1800-12-28'))) AS DATE,
FLOOR(entrytime/3600/100) AS HOURS ,
FLOOR(entrytime/3600%100*60/100) AS MINUTES
from mytable
Result :
| entrydate | entrytime | DATE | HOURS | MINUTES |
|---|---|---|---|---|
| 77337 | 4477877 | 2012-09-24 | 12 | 25 |
| 77418 | 3135704 | 2012-12-14 | 8 | 42 |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论