SQLite Sample table with Date column

SQLite



  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 current week
  14. Display all records of previous week
  15. Display all records of first week of the year
  16. Display all records of working days ( Monday to Friday ) of current week of the year
  17. 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
import sqlite3
my_conn = sqlite3.connect('test2_db') # Change your database name here
print("Connected to database successfully")

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 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
Subscribe to our YouTube Channel here

Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com



    Post your comments , suggestion , error , requirements etc here





    Python Video Tutorials
    Python SQLite Video Tutorials
    Python MySQL Video Tutorials
    Python Tkinter Video Tutorials
    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2022 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer