sql converting int to datetime, overflow issues Arithmetic overflow error converting expression to data type varchar

Question

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.

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

Answer 1

Can you try this,

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

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

Answer 2

This should work:

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

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

Demo

Answer 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)