How to Subtract X days from a Date Column in a Table ?

One of the Columns in a Table Called Stocks is ‘Date’ which is a String in the form YYMMDD. I want to subtract X days from this Date Column on the fly every time the Query is executed.

Here is a snippet from a Query that Selects Rows only from February 24 to March 6:

SELECT Stock, Date, Vol, PE FROM Stocks

WHERE SUBSTR(Stock,1,1) > '#'

AND SUBSTR(Date,3,4) >= '0224' AND SUBSTR(Date,3,4) <= '0306'

AND Vol > 1

AND PE >= 0;

I would like to change this to Select Rows from X Days ago to 'Today' every time this Query is run. Something like:

/* How to Define a String Variable = to 30 */

SET @X = "30"

/* How to Set a String Variable to 'Today' in the from of MMDD */

TDay = SELECT SUBSTR(strftime('%Y%m%d', 'now', 'localtime'		' days'),3,6); */

/* How to Set a String Variable to ('Today' - X Days) in the from of MMDD */

TXDays = SELECT SUBSTR(strftime('%Y%m%d', 'now', 'localtime', -X		' days'),3,6);

So now the Query would look something like this:

SELECT Stock, Date, Vol, PE FROM Stocks

WHERE SUBSTR(Stock,1,1) > '#'

AND Date >= 'TXDays' AND Date <= 'TDay'

AND Vol > 1

AND PE >= 0;

Thanks, M....

Firstly, "Date" is a reserved word in SQLite (I think that is true for MySQL too) so if you insist to use it as a column name it has to be quoted.

To get a valid date from the column, you can do this:

SELECT Stock, date(
         '20' || 
         SUBSTR("Date",1,2) || '-' || 
         SUBSTR("Date",3,2) || '-' || 
         SUBSTR("Date",5,2)
       ) AS fulldate
  FROM Stocks

From here, getting the day preceding this date by x days is trivial and can be done in several ways, such as transforming to-and-from a juliandate value.
(I'm using 10 days as the X in this test)

Style: newdate = date(( julianday of originaldate ) - 10 )

SELECT Stock, date(julianday('20' ||
         SUBSTR("Date",1,2) || '-' ||
         SUBSTR("Date",3,2) || '-' ||
         SUBSTR("Date",5,2)
       ) - 10) AS newdate
  FROM Stocks

Another option - Use the SQLite date-modifier capability by simply subtracting X-days:

Style: newdate = date(originaldate, '-10 days')

SELECT Stock, date('20' ||
         SUBSTR("Date",1,2) || '-' ||
         SUBSTR("Date",3,2) || '-' ||
         SUBSTR("Date",5,2), 
       '-10 days') AS newdate
  FROM Stocks

Here is one way to select rows based on the calculated date being in a specific range relative to today (using last 10 days in t his example):

SELECT *
  FROM Stocks
 WHERE date('20' ||
         SUBSTR("Date",1,2) || '-' ||
         SUBSTR("Date",3,2) || '-' ||
         SUBSTR("Date",5,2)
       ) BETWEEN date('now', '-10 days') AND date('now')

And finally - here is a quick self-contained query you can paste in any SQLite to confirm that the above is working correctly:
(or simply remove the WITH clause to use it on the original table)

WITH Stocks(Stock,"Date") AS (
    SELECT 'ABC001', '211215' UNION ALL
    SELECT 'ABC002', '220130' UNION ALL
    SELECT 'ABC003', '220203' UNION ALL
    SELECT 'ABC003', '220303'
)
SELECT Stock, "Date" as originaldate,
       date(julianday('20' ||
         SUBSTR("Date",1,2) || '-' ||
         SUBSTR("Date",3,2) || '-' ||
         SUBSTR("Date",5,2)
       ) - 10) AS newdateJulian,
       date('20' ||
         SUBSTR("Date",1,2) || '-' ||
         SUBSTR("Date",3,2) || '-' ||
         SUBSTR("Date",5,2),
       '-10 days' ) AS newdateInternal,
       date('20' ||
         SUBSTR("Date",1,2) || '-' ||
         SUBSTR("Date",3,2) || '-' ||
         SUBSTR("Date",5,2)
       ) BETWEEN date('now', '-10 days') AND date('now') AS isRecentDate
  FROM Stocks
;

-- Stock	originaldate	newdateJulian	newdateInternal	isRecentDate
-- ABC001	211215		2021-12-05	2021-12-05	0
-- ABC002	220130		2022-01-20	2022-01-20	0
-- ABC003	220203		2022-01-24	2022-01-24	0
-- ABC003	220303		2022-02-21	2022-02-21	1

What is the '20' after: AND date('20' ??

It is the missing part of the year. A year has 4 digits. Two digit years only existed 2000 years ago.

How can I set up a variable like 'X' to contain the number of Days i.e ) BETWEEN date('now', '-X days') AND date('now') /* Back '-X' Days */ ?

That would be date('now', x || ' days) where x is the number of days to add.

Also, do not forget that 'now' is an instant time in UTC so date('now') is the date at the Prime Meridian (Grenwich, England) right now. It is not the date in localtime. You need to specify that you want the 'localtime' date('now','localtime') if you want the date at your meridian (or what the computer executing the code has been set to use as its meridian).

What is the '20' after: AND date('20' ??

What Keith said, and I'd like to point out that I just assumed your dates would be close to current (year 20xx) and that this method would obviously NOT work if you have 2-digit dates dates more than 22 years ago or 78 or more years in the future.

Older systems with 2-digit dates used an assumption of year-proximity-to-current to decide.

Mostly a proximity of +50/-50 was used so that if the 2-digit year is more than 50 higher than current, the century is assumed to be previous century and if the date is more than 50 years lower than current, it's assumed to fall in the next century. Some used a specific cut-off date, like years > 70 = 1970..1999 and years < 70 = 2000..2069, etc.

Hard date-boundaries (like the 1970 example) had the disadvantage that it sets a hard-limit on the lifespan of the project/software, whereas the proximity +/- x years has the disadvantage that it needs extra code to deal with years close to century boundaries, for obvious reasons.

The 1950 or 1970 cut-offs were by far the prevailing use-cases as far as I know from my limited experience, but people who knew their systems only dealt with historic dates up to current and never future dates would often use a +10/-90 proximity so that if the current 2-digit year is "22" then it is assumed that any year higher than "32" (22+10), such as "35" means previous century (i.e. "1935") as opposed to "2035".

In your own case you can decide if you even need to worry about that and if so, what the life-span of your project is expected to be. Let's say your project needs to work for the next 30 years, until 2052, I'd add some margin and set the cut-off at 2060 and then decide that any 2-digit dates after "60", like 64, means 1964 and not 2064. To make this sort of a thing work in your case would need something like this:

date(
CASE WHEN CAST(SUBSTR("Date",1,2) AS INT) > 60 THEN '19' ELSE '20' END ||
SUBSTR("Date",1,2) ||'-'||
SUBSTR("Date",3,2) ||'-'||
SUBSTR("Date",5,2)
)

But if you don't work with dates far in the past (i.e. before 2000) then I would honestly just use the '20' and your project will work until December 31, 2099.

So .... BETWEEN date('now', '-30 days') ...

would change to ....BETWEEN date('now', X)....??

And I assume X would be defined as '-30 days' ?

Would I define it like this:

set NumDays = '-30 days'

and ....BETWEEN date('now', @NumDays)....??

Thanks, M....

You have to pass in the value somehow. How you do that depends on what you are doing. It can be a bound variable, come from some table source, or what have you. The modifier for applying a days difference is 'n days' where n is the number of days. For example, to subtract 7 days the value of n would be -7, and the modifier is a text string '-7 days'. Assuming that you have a something (field, variable, bound paramter, whatever) that contains an integer number of days to add (that means that if the value is negative you are adding a negative number -- subtracting a positive number of days) then you would use the form: datetime('now', ? || ' days') for an anonymous positional parameter, datetime('now', :days || ' days') for a named parameter days, or datetime('now', X || ' days') if X were a defined column source in some table defined in the query.

SQL does not do set. So, if you were using a named paramater NumDays that contained an integer number of days, then the form datetime('now', @NumDays || ' days')...of course, if the parameter were a text string containing the whole modifier (rather than just the number of days) then datetime('now', @NumDays) would work.