SQLite Sample table with Date column

1. Display all records of today's date
2. Display all records of yesterday
3. Display all records of tomorrow
4. Display all records of last 2 days ( no future dates )
5. Display all records of next 3 days ( no previous dates )
6. Display all records of Previous 10 days to previous 5 days
7. Display all records of next 5 days to next 10 days
8. Display all records of current month .
9. Display all records of previous month
10. Display all records of current month till today
11. Display all records of Jan month
12. Display all records of current year
13. Display all records of the year 2022
14. Display all records of current week
15. Display all records of previous week
16. Display all records of first week of the year
17. Display all records of working days ( Monday to Friday ) of current week of the year
18. 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
%%	%

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)

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)

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

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'"

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) "