How to change month in sqlite

Question

Month start from the 16/09/2019 and ends 15/10/2019 If the fate is the 11/10/2019 i want the month to be 9 instead of 10..it works in vb =(Month(Fields!Id.invdate.value)+IIF(Day(Fielfs!Id.invdate.value)<16,9,10) Mod 12+1

HOW to do in sqlite SELECT DISTINCT t1.AccNo,t1.InvDate,ifnull((SELECT MIN(t1min.InvDate) FROM t1 AS t1min WHERE t1min.InvDate > t1.InvDate AND t1.AccNo = t1min.AccNo), DATE(t1.InvDate,'start of month','+1 month', '0 day')) AS NextDate,

Strftime('%m',t1.InvDate) AS Period 'This is were if date 16/09 period is 9 And if date 11/10 period is showing 10 instead of 9

Answer 1

With SQLite you could use something like :-

WITH 
    cte_month_start AS (SELECT 16) /* THE START DAY FOR THE MONTH */,

    /* REFORMAT THE DATE TO YYYY-MM-DD format*/
    cte_reformatted_date(rdate) AS (
        SELECT(substr(mydate,7,4)||'-'||substr(mydate,4,2)||'-'||substr(mydate,1,2)) 
        FROM mytable
        ),

    /* SUBTRACT 1 FROM THE MONTH IF THE DAY OF THE MONTH IS LESS THAN THE START DAY */
    cte_adjusted_date(adate) AS (
        SELECT  
            CASE 
                WHEN CAST(strftime('%d',rdate) AS INTEGER) < (SELECT * FROM cte_month_start) 
                THEN date(rdate,'-1 months')
                ELSE rdate
            END
        FROM cte_reformatted_date
        )
SELECT substr(adate,9,2)||'/'||substr(adate,6,2)||'/'||substr(adate,1,4) AS mydate FROM cte_adjusted_date;

This uses 3 Common Table Expressions (temporary tables available within the query)

• The first is the start day of the month and facilitates the potential for flexibility

  • i.e. the start day of the month could easily be passed and thus be varied.

• The second reformats the date to one that can easily be manipulated by SQLites Date And Time Functions

• The third utilises the former CTE's to output either the un-changed date (if the day of the month is not less than the start day of the month) or the date adjusted by subtracting 1 month from the date if the day of the month is less than the start date.

• You don't actually need CTE's they could be embedded, but using them can make the code easier to understand.

With SQLite if dates are stored in a recognised format (as per the link) then manipulation of the dates is simpler.

Example/Demo

/* PREPARE TEST ENVIROMENT BY CREATING AND POPULATING TABLES */
DROP TABLE IF EXISTS mytable;
CREATE TABLE If NOT EXISTS mytable(mydate TEXT);
INSERT INTO mytable VALUES 
    ('16/09/2019'),('17/09/2019'),('18/09/2019'), /* DATES UNCHANGED */
    ('01/10/2019'),('02/10/2019'),('11/10/2019'), /* DATES THAT WILL BE ADJUSTED (month reduced)*/
    ('16/08/2019'),('11/09/2019') /* Other dates */
    ;

/* THE ACTUAL TEST */
WITH 
    cte_month_start AS (SELECT 16) /* THE START DAY FOR THE MONTH */,

    /* REFORMAT THE DATE TO YYYY-MM-DD format*/
    cte_reformatted_date(rdate) AS (
        SELECT(substr(mydate,7,4)||'-'||substr(mydate,4,2)||'-'||substr(mydate,1,2)) 
        FROM mytable
        ),

    /* SUBTRACT 1 FROM THE MONTH IF THE DAY OF THE MONTH IS LESS THAN THE START DAY */
    cte_adjusted_date(adate) AS (
        SELECT  
            CASE 
                WHEN CAST(strftime('%d',rdate) AS INTEGER) < (SELECT * FROM cte_month_start) 
                THEN date(rdate,'-1 months')
                ELSE rdate
            END
        FROM cte_reformatted_date
        )
/* RESULTS OF THE TEST */
SELECT substr(adate,9,2)||'/'||substr(adate,6,2)||'/'||substr(adate,1,4) AS mydate FROM cte_adjusted_date;
DROP TABLE IF EXISTS mytable; /* CLEAN UP TEST ENVIRONMENT */

Result :-

As can be seen the highlighted rows have been adjusted accordingly.