Search code examples
sqlsql-serversql-convert

Int to date in SQL

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

Solution

  • This works for me in SQL Server 2017:

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

    Output:

    DateResult
2019-04-30