将整数转换为datetime [英] Convert integer to datetime

问题描述

大家好，我有一个SQL Server表，其中日期存储为整数值，例如1473861331如何将它们转换为日期/日期时间？



< b>我尝试了什么：

 声明  @ mydate 整数
 
 选择  @mydate  =  1473861331  
 
  SELECT  CAST（CAST（< span class =code-sdkkeyword> @ mydate   AS   VARCHAR ） AS   DATETIME ）
 
但我收到此错误
 
转换失败 转换日期和/或时间 < span class =code-keyword>来自字符串。

解决方案

假设您的日期时间存储在Epoch& Unix时间戳格式，如果不是这样，请忽略

声明@mydate整数
设置@mydate = 1473861331 
选择DATEADD（小时，-5 ，（dateadd（第二，@ mydate，'1/1/1970'）））
 
  - 结果：2016-09-14 08：55：31.000 

从我在你的问题上留下的评论继续前进，基本上SQL可以让你将数据从一种格式转换为另一种格式，但为此你需要学习如何做到这一点：每个SQL语言开发人员（Oracle，SQL Server，MySQL）对这些功能有自己的实现。这就是为什么，有时很难帮助你，无论如何都要尝试以下代码， http：/ /stackoverflow.com/questions/3855867/convert-int-to-datetime-sql [ ^ ]。



最后，因为你用SQL Server标记了它（带有一个特殊的逗号，故意，或许？）请参阅此MSDN指南了解有关 CAST 和 CONVERT SQL函数的更多信息：https://msdn.microsoft.com/en-us/library/ms187928.aspx [ ^ ]

Take看看这些链接：

http://stackoverflow.com/问题/ 3855867 / CON vert-int-to-datetime-sql

http：/ /www.sqlservercentral.com/Forums/Topic1547707-391-1.aspx

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70515

Hi all, I have a SQL Server table where dates are stored as integer values such as 1473861331 how do I convert them to a Date / DateTime ?

What I have tried:

declare @mydate integer

select @mydate = 1473861331

SELECT CAST(CAST(@mydate AS VARCHAR) AS DATETIME)

But I receive this error

Conversion failed when converting date and/or time from character string.

解决方案

Assuming your datetime stored in Epoch & Unix Timestamp format, please ignore if this is not the case

declare @mydate integer
set @mydate = 1473861331
select DATEADD(hour,-5,(dateadd(second ,@mydate, '1/1/1970')))

--Result: 2016-09-14 08:55:31.000

Moving onward from the comment I left on your question, basically SQL can let you convert data from one format to another, but for that you would need to learn how to do that: Every SQL language developer (Oracle, SQL Server, MySQL) have their own implementations for these functions. That is why, it is sometimes hard to help you out, anyways try the following codes, http://stackoverflow.com/questions/3855867/convert-int-to-datetime-sql[^].

Finally, since you tagged it with SQL Server (with a special comma, intentionally, perhaps?) see this MSDN guide for more on CAST and CONVERT functions of SQL: https://msdn.microsoft.com/en-us/library/ms187928.aspx[^]

Take a look at these links:
http://stackoverflow.com/questions/3855867/convert-int-to-datetime-sql
http://www.sqlservercentral.com/Forums/Topic1547707-391-1.aspx
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70515

这篇关于将整数转换为datetime的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！