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 current year
  12. Display all records of current week
  13. Display all records of previous week
  14. Display all records of first week of the year
  15. Display all records of working days ( Monday to Friday ) of current week of the year
  16. Display all records of working days till today of current week of the year
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 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


plus2net.com



Post your comments , suggestion , error , requirements etc here




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