List of months between dates SQL

I have been looking for SQL code to create list of months between dates. The solution is simple and was looked up from sql - How to get a list of months between two dates in mysql - Stack Overflow and thereafter modified little.

Copy the code below and CTRL+V it on v13 Frappe Framework site to create a Report for your quick reference.

I hope it helps others

{
    "name": "List of months between dates",
    "owner": "Administrator",
    "creation": "2022-05-28 22:37:22.029452",
    "modified": "2022-05-28 23:08:22.029502",
    "modified_by": "Administrator",
    "idx": 0,
    "docstatus": 0,
    "report_name": "List of months between dates",
    "ref_doctype": "Company",
    "is_standard": "No",
    "module": "Setup",
    "report_type": "Query Report",
    "add_total_row": 1,
    "disabled": 0,
    "disable_prepared_report": 0,
    "prepared_report": 0,
    "query": "SELECT   DATE_FORMAT(m1, '%%b %%Y') AS \"Formatted Date\",\r\n         m1                         AS \"Date:Date:\"\r\nFROM     (\r\n                SELECT (%(from_date)s - interval dayofmonth(%(from_date)s)-1 day) + interval m month AS m1\r\n                FROM   (\r\n                              SELECT @rownum := @rownum + 1 AS m\r\n                              FROM   (\r\n                                            SELECT 1\r\n                                            UNION\r\n                                            SELECT 2\r\n                                            UNION\r\n                                            SELECT 3\r\n                                            UNION\r\n                                            SELECT 4\r\n                                            UNION\r\n                                            SELECT 5\r\n                                            UNION\r\n                                            SELECT 6) t1,\r\n                                     (\r\n                                            SELECT 1\r\n                                            UNION\r\n                                            SELECT 2\r\n                                            UNION\r\n                                            SELECT 3\r\n                                            UNION\r\n                                            SELECT 4\r\n                                            UNION\r\n                                            SELECT 5\r\n                                            UNION\r\n                                            SELECT 6) t2,\r\n                                     (\r\n                                            SELECT 1\r\n                                            UNION\r\n                                            SELECT 2\r\n                                            UNION\r\n                                            SELECT 3\r\n                                            UNION\r\n                                            SELECT 4\r\n                                            UNION\r\n                                            SELECT 5\r\n                                            UNION\r\n                                            SELECT 6) t3,\r\n                                     (\r\n                                            SELECT 1\r\n                                            UNION\r\n                                            SELECT 2\r\n                                            UNION\r\n                                            SELECT 3\r\n                                            UNION\r\n                                            SELECT 4\r\n                                            UNION\r\n                                            SELECT 5\r\n                                            UNION\r\n                                            SELECT 6) t4,\r\n                                     (\r\n                                            SELECT @rownum := -1) t0 ) d1 ) d2\r\nWHERE    m1 <= %(to_date)s\r\nORDER BY m1",
    "doctype": "Report",
    "filters": [{
        "name": "1b16a41d82",
        "owner": "Administrator",
        "creation": "2022-05-28 22:37:22.029452",
        "modified": "2022-05-28 23:08:22.029502",
        "modified_by": "Administrator",
        "parent": "List of months between dates",
        "parentfield": "filters",
        "parenttype": "Report",
        "idx": 1,
        "docstatus": 0,
        "fieldname": "from_date",
        "label": "From Date",
        "fieldtype": "Date",
        "mandatory": 1,
        "wildcard_filter": 0,
        "doctype": "Report Filter"
    }, {
        "name": "e280d76423",
        "owner": "Administrator",
        "creation": "2022-05-28 22:37:22.029452",
        "modified": "2022-05-28 23:08:22.029502",
        "modified_by": "Administrator",
        "parent": "List of months between dates",
        "parentfield": "filters",
        "parenttype": "Report",
        "idx": 2,
        "docstatus": 0,
        "fieldname": "to_date",
        "label": "To Date",
        "fieldtype": "Date",
        "mandatory": 1,
        "wildcard_filter": 0,
        "doctype": "Report Filter"
    }],
    "columns": [],
    "roles": [{
        "name": "5bef9ab93b",
        "owner": "Administrator",
        "creation": "2022-05-28 22:37:22.029452",
        "modified": "2022-05-28 23:08:22.029502",
        "modified_by": "Administrator",
        "parent": "List of months between dates",
        "parentfield": "roles",
        "parenttype": "Report",
        "idx": 1,
        "docstatus": 0,
        "role": "Stock Manager",
        "doctype": "Has Role"
    }]
}
3 Likes

Only applicable for finding dates between 1296 months. Beyond that, you have to add lines to the union tables.

1 Like