SQLite version 3.31.1, Ubuntu 20.04

Hi.

I have a table that has MMDD (string) as a column. Is there a way to do math on this Date field? For example, I would like to subtract 30 days from MMDD.

Thanks, M...

Look at the right side of this page.

There are several ways, some examples below.

SQLite provides a whole bunch of date-time calculations that can be performed on valid date/time values - so the best route is to convert the MMDD to a proper date by taking apart the month and day and appending it to a valid year.

Since we do not know the real year, the best is to assume not a leap-year. I chose year 2100 in the calcs sine it is four years away from any leap year, so calcs spanning a year or two won't be affected.

(Why exactly year 2100 is 4-years away from any leap-year, just like the year 1900, is off-topic here, but an easy google search)

In the SQL below I make a table and fill it with such MMDD dates, then show how to compose said full date from the MMDD, and then the last queries use that date in calculations - the -30 days asked for, and some other examples. I also show different output options how to print the calculated dates.

Most of what was used can be found on this page: www.sqlite.org/lang_datefunc.html

CREATE TABLE mdTable(
  id INT,
  mdText TEXT
);

INSERT INTO mdTable(id, mdText) VALUES
 (1, '0101')
,(2, '0215')
,(3, '0319')
,(4, '0420')
,(5, '0521')
,(6, '0629')
,(7, '1030')
,(8, '1101')
;


  -- First, a method that definitely doesn't work :)

SELECT id, mdText, mdText - 30
  FROM mdTable
;

  --      id     |   mdText   | mdText - 30
  -- ------------|------------|------------
  --       1     |    0101    |     71     
  --       2     |    0215    |     185    
  --       3     |    0319    |     289    
  --       4     |    0420    |     390    
  --       5     |    0521    |     491    
  --       6     |    0629    |     599    
  --       7     |    1030    |    1000    
  --       8     |    1101    |    1071    


-- Showing splitting the MM and DD and then Composing valid date "aDate"

SELECT id, mdText, 
       substr(mdText,1,2) AS Mth, 
       substr(mdText,3,2) AS Day,
       date('2100-' || substr(mdText,1,2) || '-' || substr(mdText,3,2)) AS aDate
  FROM mdTable
;

  --      id     |   mdText   |     Mth    | Day| aDate       
  -- ------------|------------|------------|----|------------
  --       1     |    0101    |     01     | 01 | 2100-01-01  
  --       2     |    0215    |     02     | 15 | 2100-02-15  
  --       3     |    0319    |     03     | 19 | 2100-03-19  
  --       4     |    0420    |     04     | 20 | 2100-04-20  
  --       5     |    0521    |     05     | 21 | 2100-05-21  
  --       6     |    0629    |     06     | 29 | 2100-06-29  
  --       7     |    1030    |     10     | 30 | 2100-10-30  
  --       8     |    1101    |     11     | 01 | 2100-11-01  


  -- Composing aDate again but in a sub-query and using it in the main query:

SELECT id, mdText, 
       substr(date(aDate, '-30 day'),6,5) AS 'DT-30d',
       strftime('%m%d', date(aDate, '-15 day')) AS 'DT-15d'
  FROM (SELECT id, mdText, date('2100-' || substr(mdText,1,2) || '-' || substr(mdText,3,2)) AS aDate
          FROM mdTable
       ) AS md
;

  --      id     |   mdText   |DT-30d |DT-15d
  -- ------------|------------|-------|------
  --       1     |    0101    |12-02  | 1217 
  --       2     |    0215    |01-16  | 0131 
  --       3     |    0319    |02-17  | 0304 
  --       4     |    0420    |03-21  | 0405 
  --       5     |    0521    |04-21  | 0506 
  --       6     |    0629    |05-30  | 0614 
  --       7     |    1030    |09-30  | 1015 
  --       8     |    1101    |10-02  | 1017 


  -- Example removing 2 months and adding 5 days in one calculation:

SELECT id, mdText, 
       strftime('%m%d', date(aDate, '-2 months', '+5 days')) AS 'DT-2m+5d'
  FROM (SELECT id, mdText, date('2100-' || substr(mdText,1,2) || '-' || substr(mdText,3,2)) AS aDate
          FROM mdTable
       ) AS md
;

  --      id     |   mdText   |DT-2m+5d
  -- ------------|------------|--------
  --       1     |    0101    |  1106  
  --       2     |    0215    |  1220  
  --       3     |    0319    |  0124  
  --       4     |    0420    |  0225  
  --       5     |    0521    |  0326  
  --       6     |    0629    |  0504  
  --       7     |    1030    |  0904  
  --       8     |    1101    |  0906  

Hope that offers some insight - Good luck!

That is awesome! Great answer to my post.

While I have you on the line, another question:

Given this command: SELECT datetime('now','localtime');

The system returns: 2022-02-19 12:48:18.

I want to be able to Subtract X days from this, and end up with a String in this Format: YYMMDD which is Today less X days

Example: Today is 2022-02-19. 30 days ago is 2022-01-20 I want to end up with 220120 in a string

Thanks, M....

You mean like:

select substr(strftime('%Y%m%d', 'now', 'localtime', -7 || ' days'),3,6);

where "-7" is the X ...