DenStudent DenStudent - 1 year ago 64
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 Source

Try following query

    stadate > CAST(CONVERT(varchar(8),getdate(),112) AS INT)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download