Subtract substring days from date

Question

I'm trying to come up with a Select statement that would return the DATE column minus the number of days in the DAYS column.

This is the query I tried:

SELECT 
    DATEADD(DAY, -+(REPLACE(ISNULL(DAYS,0), 'DAYS', '')), DATE) 
FROM
    T

It throws an error

Operand data type varchar is invalid for minus operator

What are you trying to do with the "-+" before the `REPLACE`? — Tab Alleman, Oct 23 '18 at 19:34
[Subtract two dates stored as varchar](https://dba.stackexchange.com/q/132391/141074), [convert varchar to date and subtract it from another date](https://stackoverflow.com/q/44637627/608639), [How to subtract two varchar columns that contain time value as input](https://stackoverflow.com/q/43443421/608639), [SQL subtract date as varchar site:stackoverflow.com](https://www.google.com/search?q=SQL+subtract+date+as+varchar+site%3Astackoverflow.com), etc. — jww, Oct 24 '18 at 04:55

score 2 · Answer 1 · answered Oct 23 '18 at 19:38

2

dateadd(day, -1*convert(int, coalesce(replace([DAYS], ' days', ''),'0')), [DATE])

answered Oct 23 '18 at 19:38

Dale K

25,246
15
42
71

score 2 · Answer 2 · answered Oct 23 '18 at 19:40

Something like

DECLARE
       @Days VARCHAR(25) = '23 DAYS',
       @Date DATE = '2018-10-23';

SELECT DATEADD(Day, - CAST(REPLACE(@Days , 'DAYS', '') AS INT), @Date )

If you are using SQL Server 2012+

SELECT DATEADD(Day, - ISNULL(TRY_CAST(REPLACE(@Days , 'DAYS', '') AS INT), 0), @Date )

score 1 · Answer 3 · edited Jun 01 '20 at 22:46

1

The DATEADD function expects an integer for its second parameter argument.

By REPLACING 'DAYS' with an empty string, you are still left with a varchar containing a number and the blank space that was between that number and the word "DAYS".

RTRIM this result and CAST it as an Integer, and you should be good to go.

Oh, and you also need to put ,DATE inside the DATEADD()'s parenthesis.

edited Jun 01 '20 at 22:46

Dale K

25,246
15
42
71

answered Oct 23 '18 at 19:37

Tab Alleman

31,483
7
36
52

**[Does the blank space really matter?](https://rextester.com/OWRBN5153)** – Ilyes Oct 23 '18 at 19:54

score 1 · Answer 4 · answered Oct 23 '18 at 19:41

1

I think you need to get the number, cast it to integer as negative and use dateadd like below:

SELECT dateadd(DAY,cast('-'+substring([DAYS], 1,charindex(' ',[DAYS])) as int),[DATE])
FROM T

answered Oct 23 '18 at 19:41

Eray Balkanli

7,752
11
48
82

score 1 · Answer 5 · answered Oct 23 '18 at 19:48

1

You can use this:

Use CHARINDEX to get the number of days and convert to int and then minus it from date column

select DATEADD(DAY,-CAST(SUBSTRING(days,1,CHARINDEX(' ',days)) AS INT),DATE),* from #table

answered Oct 23 '18 at 19:48

GGadde

391
1
14

Subtract substring days from date

5 Answers5