SQLite Date query

sqlite> .open E:/sqlite3_mgmt/my_date.db
Copy a fresh SQL dump and use the same to create the table with all records.
Note that my_dt is the text datatype column in which we stored all our date values.
Output will change based on the date of SQL dump you used.

PHP Script to connect and get data from SQLite database table

#### FOR SQLite Database ######
// This will create the database if not exists in the same location where the script is running.
// For a different location give the path details. 
// use the SQL dump to create table inside the database. 
//$my_conn = new PDO('sqlite:D:\\sqlite-data\\my_date.db');// different path
$my_conn = new PDO('sqlite:'.dirname(__FILE__).'/my_date.db'); // same location
#### end of SQLite database connection #####

$query="SELECT record_id,my_dt,month_year FROM table_dt WHERE my_dt=DATE('now')";

echo "<table><tr><th>record_id</th><th>Date</th>
foreach($my_conn->query($query) as $row){
echo "<tr><td>$row[record_id]</td><td>$row[my_dt]</td>
echo "</table>";
OR You can use SQLite3, the CLI to manage database

All records of today.
sqlite> SELECT * FROM table_dt WHERE my_dt=DATE('now');
All records of last day of current month.
SELECT * FROM table_dt WHERE my_dt=DATE('now','start of month','+1 month','-1 day');

Last three days including today's record.
SELECT * FROM table_dt WHERE my_dt 
	BETWEEN DATE('now','-3 day') AND DATE('now');
Last 10 days to last 3 days
SELECT * FROM table_dt WHERE my_dt 
	BETWEEN DATE('now','-10 day') AND DATE('now','-3 day');

Present Month Year or Day

Current Month of all year
SELECT * FROM table_dt WHERE STRFTIME('%m',my_dt)=STRFTIME('%m','now');
Current Month of this year
SELECT * FROM table_dt WHERE STRFTIME('%Y-%m',my_dt)=STRFTIME('%Y-%m','now');
All records of current year
SELECT * FROM table_dt WHERE STRFTIME('%Y',my_dt)=STRFTIME('%Y','now');

Previous Year , Month and day

All records of Previous Year
SELECT * FROM table_dt WHERE STRFTIME('%Y',my_dt)=STRFTIME('%Y','now','-1 year');
All records of Previous Year current month
SELECT * FROM table_dt WHERE STRFTIME('%Y-%m','now','-1 year') = STRFTIME('%Y-%m',my_dt);
All records of Previous year same date ( month )
SELECT * FROM table_dt WHERE STRFTIME('%Y-%m-%d','now','-1 year') = STRFTIME('%Y-%m-%d',my_dt);
ALL records of Previous month same date
SELECT * from table_dt WHERE STRFTIME('%Y-%m-%d','now','-1 month') = STRFTIME('%Y-%m-%d',my_dt);

Matching Weekday of the year

Present week of all the years.
SELECT * FROM table_dt WHERE STRFTIME('%W','now')=STRFTIME('%W',my_dt);
Present week of of this Year
SELECT * FROM table_dt WHERE STRFTIME('%W-%Y','now')=STRFTIME('%W-%Y',my_dt);
Next week week of this Year
SELECT * FROM table_dt WHERE STRFTIME('%W-%Y','now','weekday 1')=STRFTIME('%W-%Y',my_dt);
Present Weekday of current month and Year
SELECT * FROM table_dt WHERE STRFTIME('%Y-%m-%w','now')=STRFTIME('%Y-%m-%w',my_dt);

Subscribe to our YouTube Channel here


* indicates required
Subscribe to plus2net


    Post your comments , suggestion , error , requirements etc here

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