如何在 SQL Server 2008 中将 int 转换为日期
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7830002/
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
How to convert int to date in SQL Server 2008
提问by Vikrant More
I am using MS SQL Server 2008, and in a table I have column idate as date but in the integer format. But in the query I want that in the date format. Is it possible to convert integer date into proper datetime format?
我正在使用 MS SQL Server 2008，并且在一个表中，我将列 idate 作为日期，但采用整数格式。但在查询中，我希望采用日期格式。是否可以将整数日期转换为正确的日期时间格式？
回答by davey
You can't convert an integer value straight to a date but you can first it to a datetime then to a date type
您不能将整数值直接转换为日期，但可以先将其转换为日期时间，然后再转换为日期类型
select cast(40835 as datetime)
select cast(40835 as datetime)
and then convert to a date (SQL 2008)
然后转换为日期（SQL 2008）
select cast(cast(40835 as datetime) as date)
select cast(cast(40835 as datetime) as date)
cheers
干杯
回答by Nikhil Gutha
You have to first convert it into datetime, then to date.
您必须先将其转换为日期时间，然后再转换为日期。
Try this, it might be helpful:
试试这个，它可能会有所帮助：
Select Convert(DATETIME, LEFT(20130101, 8))
then convert to date.
然后转换为日期。
回答by qxotk
You most likely want to examine the documentation for T-SQL's CAST and CONVERTfunctions, located in the documentation here: http://msdn.microsoft.com/en-US/library/ms187928(v=SQL.90).aspx
您很可能想查看 T-SQL 的CAST 和 CONVERT函数的文档，位于此处的文档中：http: //msdn.microsoft.com/en-US/library/ms187928(v=SQL.90).aspx
You will then use one of those functions in your T-SQL query to convert the [idate] column from the database into the datetime format of your liking in the output.
然后，您将在 T-SQL 查询中使用这些函数之一将数据库中的 [idate] 列转换为输出中您喜欢的日期时间格式。
回答by Anthony Simon Mielniczuk
Reading through this helps solve a similar problem. The data is in decimal datatype - [DOB] [decimal](8, 0) NOT NULL - eg - 19700109. I want to get at the month. The solution is to combine SUBSTRING with CONVERT to VARCHAR.
通读本文有助于解决类似的问题。数据采用十进制数据类型 - [DOB] [decimal](8, 0) NOT NULL - 例如 - 19700109。我想得到月份。解决方案是将 SUBSTRING 与 CONVERT 结合到 VARCHAR。
SELECT [NUM]
,SUBSTRING(CONVERT(VARCHAR, DOB),5,2) AS mob
FROM [Dbname].[dbo].[Tablename]
回答by Vitaliy A
If your integer is timestamp in milliseconds use:
如果您的整数是以毫秒为单位的时间戳，请使用：
SELECT strftime("%Y-%d-%m", col_name, 'unixepoch') AS col_name
It will format milliseconds to yyyy-mm-dd string.
它将毫秒格式化为 yyyy-mm-dd 字符串。
