CODEWITHSUNDEEP
sql-server
satishkadimi
satishkadimi

Reputation: 11

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

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

Upvotes: 1

Views: 59

Answers (3)

Martin Smith
Martin Smith

Reputation: 453067

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)
               )

Upvotes: 4

Sandip - Frontend Developer
Sandip - Frontend Developer

Reputation: 14669

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)
        )
        ,'-',''
    )

Upvotes: 0

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

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))

Upvotes: 0

Related Questions