DenStudent DenStudent - 4 months ago 8
SQL Question

SQL - format INT to date and add condition

I have a table with a date column. This column is an INT and contains dates in the format YYYYMMDD (for example 20160713). Now I want to get all rows where these dates are after today. I tried the following:

SELECT ID, startdate, comment
FROM comments
WHERE startdate > getdate


But that results in to following error:


Arithmetic overflow error converting expression to data type datetime.


So I tried it by formatting it to a dd/mm/yyyy format

CONVERT(VARCHAR(10), (convert(date,CONVERT(varchar(10),[startdate],103))),103) > getdate()


but that results into this error:


The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.


Any ideas how to do this?

Answer

Try following query

SELECT 
    ID, 
    startdate, 
    comment
FROM 
    comments
WHERE 
    stadate > CAST(CONVERT(varchar(8),getdate(),112) AS INT)