Int to date in SQL

Question

I try to convert the column [policyeffective_date] which looks like YYYYMMDD (for example 20190430) into a date in SQL Server.

I tried a few solutions find on the forum, but none worked. Currently I have the following code and errors:

SELECT 
    CONVERT(DATETIME, policyeffective_date, 104) AS test 
FROM 
    [DATAWAREHOUSE].[dbo].[blabla]

Error:

Arithmetic overflow error converting expression to data type datetime.

I also tried:

SELECT 
    CONVERT(DATE, CONVERT(NVARCHAR(8), policyeffective_date), 104)    
FROM 
    [DATAWAREHOUSE].[dbo].[blabla]

Error:

Operand type clash: int is incompatible with date

Thank you for your help

This works for me: DECLARE @policyeffective_date int = 20201201; SELECT convert(date, convert(nvarchar(8), @policyeffective_date), 104) ; — R.J. Dunnill, Jul 19 '19 at 23:15
You can use the left() to get the string of the int which will convert Select try_convert(date,left(20190430,8)) — John Cappelletti, Jul 19 '19 at 23:18
And now you will discover why storing dates as numbers (or strings) is a bad idea. — SMor, Jul 19 '19 at 23:18
Whose smart idea was it to store date as `INT`? Date is meant to be stored as `DATETIME` type, not `INT` — Eric, Jul 19 '19 at 23:23

score 1 · Answer 1 · answered Jul 20 '19 at 00:09

You would seem to have bad data in the column. Given that you are getting a run-time error and not a compile-time error, the types seem correct.

The simplest solution is try_convert():

SELECT try_convert(datetime, policyeffective_date, 104) AS test 
FROM [DATAWAREHOUSE].[dbo].[blabla];

But to find the problem, you can use:

SELECT policyeffective_date
FROM [DATAWAREHOUSE].[dbo].[blabla]
WHERE try_convert(datetime, policyeffective_date, 104) IS NULL AND
      policyeffective_date IS NOT NULL

score -1 · Accepted Answer · answered Jul 19 '19 at 23:38

-1

This works for me in SQL Server 2017:

select cast(cast(20190430 as nvarchar) as DATE) AS DateResult

Output:

DateResult
2019-04-30

answered Jul 19 '19 at 23:38

Ken White

123,280
14
225
444

Int to date in SQL

2 Answers2