CODEWITHSUNDEEP
sql-server
Muhammad Ali Khamis
Muhammad Ali Khamis

Reputation: 363

How to reduce one number from string data

How to less numeric value from data type varchar. For example, I have table which contains value like '2019-02-15', '2019-02-20 10:00:15.000', '3476', '3478956', and I need to less -1 from all of them. How can i do that.

Below is sample query

CREATE TABLE dbo.TestTable (SampleData VARCHAR(100))

INSERT INTO dbo.TestTable ([SampleData])
VALUES ('2019-02-15')
    ,('2019-02-20 10:00:15.000')
    ,('3476')
    ,('3478956')

SELECT * FROM dbo.TestTable

enter image description here

Upvotes: 1

Views: 127

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 81990

If 2012+, you can use try_convert() within a case statement

Example

SELECT *
      ,RequiredData = case when try_convert(int ,SampleData) is not null then convert(varchar(100),try_convert(int,SampleData)-1)
                           when try_convert(date,SampleData) is not null then convert(varchar(100),dateadd(DAY,-1,try_convert(date,SampleData)) )
                           end 
 FROM dbo.TestTable

Returns

SampleData              RequiredData
2019-02-15              2019-02-14
2019-02-20 10:00:15.000 2019-02-19
3476                    3475
3478956                 3478955

Upvotes: 2

Related Questions