将表达式转换为数据类型日期时间时出现算术溢出错误.(同时显示日期时间..) [英] Arithmetic overflow error converting expression to data type datetime. (while displaying date time..)

问题描述

执行时出现以下错误

declare @yr_mnth_dt as numeric;
set @yr_mnth_dt = 20130822;
select convert(datetime,@yr_mnth_dt,112) as YR_MNTH_DT

错误显示

Arithmetic overflow error converting expression to data type datetime.

推荐答案

您的问题是您正在尝试将数字 convert 转换为 datetime，而这只是不工作.

You issue is that you're trying to convert the numeric to a datetime, and this just isn't working.

你需要先把你的numeric变成一个字符串:

You need to turn your numeric into a string first:

declare @yr_mnth_dt as numeric;
set @yr_mnth_dt = 20130822;

select yr_mnth_dt = cast(cast(@yr_mnth_dt as char(8)) as datetime);

SQL Fiddle with demo.

当您尝试将数字类型转换为 datetime 时，SQL Server 尝试将数字值作为天数添加到日期 01-Jan-1900.在您的情况下，这是试图增加数百万天，因此会出现溢出错误.

When you try and convert a numeric type to a datetime, SQL Server tries to add the numeric value as the number of days to the date 01-Jan-1900. In your case this is trying to add millions of days, and hence the overflow error.

CONVERT 也可以正常工作，如果您愿意:

CONVERT works fine, too, if you prefer:

select yr_mnth_dt = convert(datetime, convert(char(8), @yr_mnth_dt));

SQL Fiddle with demo.

这篇关于将表达式转换为数据类型日期时间时出现算术溢出错误.(同时显示日期时间..)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！