Python sqlite Date query to display matching records using between queries in date column

Download this source code from Github or run it in your Google Colab platform for an interactive, hands-on experience with SQLite database queries using today's date as base date.
https://github.com/plus2net/Python-basics/blob/main/SQLite_sample_table_date_column_dump.ipynb

Display all records of today's date
Display all records of yesterday
Display all records of tomorrow
Display all records of last 2 days ( no future dates )
Display all records of next 3 days ( no previous dates )
Display all records of Previous 10 days to previous 5 days
Display all records of next 5 days to next 10 days
Display all records of current month .
Display all records of previous month
Display all records of current month till today
Display all records of Jan month
Display all records of current year
Display all records of the year 2022
Display all records of current week
Display all records of previous week
Display all records of first week of the year
Display all records of working days ( Monday to Friday ) of current week of the year
Display all records of working days till today of current week of the year

strftime() formats

%d	day of month: 00
%f	fractional seconds: SS.SSS
%H	hour: 00-24
%j	day of year: 001-366
%J	Julian day number (fractional)
%m	month: 01-12
%M	minute: 00-59
%s	seconds since 1970-01-01
%S	seconds: 00-59
%w	day of week 0-6 with Sunday==0
%W	week of year: 00-53
%Y	year: 0000-9999
%%	%

Connecting to Database using sqlite3 and applying query

import sqlite3
my_path='F:\\testing\\sqlite\\my_db.db' #Change the path of your database  
try:
    my_conn = sqlite3.connect(my_path) # 
except sqlite3.Error as my_error:
  print("error: ",my_error)
else:
    print("Connected to database successfully")

try:
    query="SELECT * FROM dt_table" # SQL to use for getting data from table 
    r_set=my_conn.execute(query)  # execute the query using the connection 
    for row in r_set:
        print(row)    # Print each row 
except sqlite3.Error as my_error:
  print("error: ",my_error)

Connecting to Database using SQLAlchemy and applying query

from sqlalchemy import create_engine 
from sqlalchemy.exc import SQLAlchemyError
my_path='F:\\testing\\sqlite\\my_db.db' #Change the path 
try:
  my_conn = create_engine('sqlite:///'+ my_path)
except SQLAlchemyError as e:
  #print(e)
  error = str(e.__dict__['orig'])
  print(error)
  
try:
    query="SELECT * FROM dt_table LIMIT 0,3" # Query to use 
    r_set=my_conn.execute(query)
    for row in r_set:
        print(row)
except SQLAlchemyError as e:
  #print(e)
  error = str(e.__dict__['orig'])
  print(error)

Display all records of today's date

query="SELECT * FROM dt_table WHERE date=date('now')"
r_set=my_conn.execute(query)
for row in r_set:
    print(row)

Output ( it will change based on your sql dump and system date value )

('0', '2020-08-19', 'Aug-2020')

For all the queries , the display part remain same and the SQL string query only changes. So in all balance scripts only the query part is shown.

Display all records of yesterday

query="SELECT * FROM dt_table WHERE date=date('now','-1 day')"

Display all records of tomorrow

query="SELECT * FROM dt_table WHERE date=date('now','1 day')"

Display all records of last 2 days ( no future dates )

query="SELECT * FROM dt_table WHERE date BETWEEN date('now','-2 day') AND date('now')"

Display all records of next 3 days ( no previous dates )

query="SELECT * FROM dt_table WHERE date BETWEEN date('now') AND date('now','3 day')"

Display all records of Previous 10 days to previous 5 days

query="SELECT * FROM dt_table WHERE date BETWEEN date('now','-10 day') AND date('now','-5 day')"

Display all records of next 5 days to next 10 days

query="SELECT * FROM dt_table WHERE date BETWEEN date('now','5 day') AND date('now','10 day')"

Display all records of current month

query="SELECT * FROM dt_table WHERE strftime('%m',date) = strftime('%m','now')"

Display all records of previous month

query="SELECT * FROM dt_table WHERE strftime('%m',date) = strftime('%m','now','-1 month')"

Display all records of current month till today

query="SELECT * FROM dt_table WHERE date BETWEEN strftime('%Y-%m-01','now') and strftime('%Y-%m-%d','now')"

Display all records of Jan month

query="SELECT * FROM dt_table WHERE strftime('%m',date)='01'"

For December month

query="SELECT * FROM dt_table WHERE strftime('%m',date)='12'"

Display all records of current year

query="SELECT * FROM dt_table WHERE strftime('%Y',date) = strftime('%Y','now')"

Display all records of the year 2022

query="SELECT * FROM dt_table WHERE strftime('%Y',date) = '2022'"

Display all records of current week

query="SELECT * FROM dt_table WHERE strftime('%W',date) = strftime('%W','now')"

Display all records of previous week

query="SELECT *  FROM dt_table WHERE CAST(strftime('%W',date) AS INTEGER) = CAST(strftime('%W','now') AS INTEGER) - 1 "

Display all records of next week

query="SELECT *  FROM dt_table WHERE CAST(strftime('%W',date) AS INTEGER) = CAST(strftime('%W','now') AS INTEGER) + 1 "

Display all records of first week of the year

query="SELECT *  FROM dt_table WHERE CAST(strftime('%W',date) AS INTEGER) =  1 "

Display all records of working days ( Monday to Friday ) of current week of the year

query="SELECT * FROM dt_table WHERE strftime('%W', date)=strftime('%W','now') and \
       CAST(strftime('%w',date) AS INTEGER) BETWEEN 1 AND 5"

Display all records of working days till today of current week of the year

query="SELECT * FROM dt_table WHERE strftime('%W', date)=strftime('%W','now') and \
       CAST(strftime('%w',date) AS INTEGER) BETWEEN 1 AND CAST(strftime('%w','now') AS INTEGER) "

View & Download sample ipynb file (.html format)

Sqlite Check Date insert update select Order By

Numpy Tkinter & SQLite Python & MySQL Python- Tutorials

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect: