In SQL Server I did't find any examples how to convert int data type to date data type

Question

I have a table with INT datatype column and my data is something like this

valid_to_day(int)
------------
20150301
20150101

I want to get previous date of this column, if I perform -1 to the above data the data is something like this

valid_to_day
------------
20150300
20150100

Expected data

valid_to_day
------------
20150228
20141231

Please can anyone help

Thanks in advance

satish

Answer 1

You shouldn't store dates like that.

The Date datatype is more compact, ensures you have no invalid dates, and can be used with date functions.

Hopefully you need this so you can fix your schema.

One method which does not rely on casting to string would be

SELECT DATEFROMPARTS(valid_to_day/10000, valid_to_day%10000/100, valid_to_day%100)

And of course now it is the proper datatype you can just use DATEADD to get the previous day.

SELECT DATEADD(DAY,
                -1, 
                DATEFROMPARTS(valid_to_day/10000, valid_to_day%10000/100, valid_to_day%100)
               ) 

Answer 2

You can use dateadd function along with convert type of 112

declare @d int = 20150301

select  convert(int, convert(varchar(10), dateadd(d,-1,convert (date, convert(varchar(10),@d), 112)),112))

Answer 3

Convert first INT to VARCHAR and then to DATE:

DECLARE @d INT = 20150101

SELECT CAST(CAST(@d AS VARCHAR(8)) AS DATE)
SELECT DATEADD(d,-1,CAST(CAST(@d AS VARCHAR(8)) AS DATE))
SELECT 
    REPLACE
    (
        CAST
        (
            DATEADD(d,-1,CAST
                        (
                            CAST(@d AS VARCHAR(8)) AS DATE)
                        ) 
            AS VARCHAR(10)
        )
        ,'-',''
    )