sql将int转换为datetime，溢出问题将表达式转换为数据类型varchar的算术溢出错误

I have a value of 20070807000000 i tried CONVERT (datetime,convert(char(8),20070807000000)) but i'm getting the error Arithmetic overflow error converting expression to data type varchar.

我有一个值20070807000000我试过CONVERT(datetime,convert(char(8),20070807000000))但我得到错误算术溢出错误将表达式转换为数据类型varchar。

What can i do in order to get this to a datetime?

我能做些什么来达到日期时间?

3 个解决方案

#1

Can you try this,

你能试试吗

select 
    CONVERT (datetime, convert(char(8), left(ltrim(20070807000000), 8)))

Result:2007-08-07 00:00:00.000

#2

This should work:

这应该工作:

SELECT CAST(SUBSTRING(CAST(20070807000000 as varchar(20)),1,8) as datetime)

Result: August, 07 2007 00:00:00+0000

结果:2007年8月7日00:00:00 + 0000

Demo

#3

An extra answer. You can provide a datetime format to your output:

一个额外的答案。您可以为输出提供日期时间格式:

SELECT  CONVERT(VARCHAR, CAST( SUBSTRING(CAST(20070807000000 AS VARCHAR),1,8) AS DATETIME ), 103)

Result: 07/08/2007

SELECT  CONVERT(VARCHAR, CAST( SUBSTRING(CAST(20070807000000 AS VARCHAR),1,8) AS DATETIME ), 111)

Result: 2007/08/07

For more information about Date formats: CAST and CONVERT (Transact-SQL)

有关日期格式的更多信息:CAST和CONVERT(Transact-SQL)

#1