将 INT 转换为 DATETIME (SQL) [英] Convert INT to DATETIME (SQL)

问题描述

我正在尝试将日期转换为日期时间，但出现错误.我要转换的数据类型是 (float,null)，我想将其转换为 DATETIME.

I am trying to convert a date to datetime but am getting errors. The datatype I'm converting from is (float,null) and I'd like to convert it to DATETIME.

此代码的第一行工作正常，但在第二行出现此错误:

The first line of this code works fine, but I get this error on the second line:

将表达式转换为数据类型日期时间时出现算术溢出错误.

Arithmetic overflow error converting expression to data type datetime.

CAST(CAST( rnwl_efctv_dt AS INT) AS char(8)),
CAST(CAST( rnwl_efctv_dt AS INT) AS DATETIME),

推荐答案

您需要先转换为 char，因为转换为 int 会将那些天添加到 1900-01-01

you need to convert to char first because converting to int adds those days to 1900-01-01

select CONVERT (datetime,convert(char(8),rnwl_efctv_dt ))

这里有一些例子

select CONVERT (datetime,5)

1900-01-06 00:00:00.000

1900-01-06 00:00:00.000

select CONVERT (datetime,20100101)

爆炸，因为您不能将 20100101 天添加到 1900-01-01 ..您超出了限制

blows up, because you can't add 20100101 days to 1900-01-01..you go above the limit

先转成字符

declare @i int
select @i = 20100101
select CONVERT (datetime,convert(char(8),@i))

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