SQLite Date query

sqlite> .open E:/sqlite3_mgmt/my_date.db

Connect to database or create database

#### 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
$my_conn->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
#### end of SQLite database connection #####

$sql= " "; // Paste the copied data ( SQL dump ) taken from above link. 
try {
$my_conn->exec($sql);
}
catch (PDOException $e) {
    echo "Failed to create table: " . $e->getMessage();
}

PHP Script to connect and get data from SQLite database table

<?Php
$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>
	<th>month-year</th></tr>";
foreach($my_conn->query($query) as $row){
echo "<tr><td>$row[record_id]</td><td>$row[my_dt]</td>
	<td>$row[month_year]</td></tr>";
}
echo "</table>";
?>

sqlite> SELECT * FROM table_dt WHERE my_dt=DATE('now');

SELECT * FROM table_dt WHERE my_dt=DATE('now','start of month','+1 month','-1 day');

SELECT * FROM table_dt WHERE my_dt 
	BETWEEN DATE('now','-3 day') AND DATE('now');

0|2023-11-07|Nov-2023
1|2023-11-06|Nov-2023
2|2023-11-05|Nov-2023
3|2023-11-04|Nov-2023

SELECT * FROM table_dt WHERE my_dt 
	BETWEEN DATE('now','-10 day') AND DATE('now','-3 day');

3|2023-11-04|Nov-2023
4|2023-11-03|Nov-2023
5|2023-11-02|Nov-2023
6|2023-11-01|Nov-2023
7|2023-10-31|Oct-2023
8|2023-10-30|Oct-2023
9|2023-10-29|Oct-2023
10|2023-10-28|Oct-2023

Present Month Year or Day

SELECT * FROM table_dt WHERE STRFTIME('%m',my_dt)=STRFTIME('%m','now');

SELECT * FROM table_dt WHERE STRFTIME('%Y-%m',my_dt)=STRFTIME('%Y-%m','now');

SELECT * FROM table_dt WHERE STRFTIME('%Y',my_dt)=STRFTIME('%Y','now');

Previous Year , Month and day

SELECT * FROM table_dt WHERE STRFTIME('%Y',my_dt)=STRFTIME('%Y','now','-1 year');

SELECT * FROM table_dt WHERE STRFTIME('%Y-%m','now','-1 year') = STRFTIME('%Y-%m',my_dt);

SELECT * FROM table_dt WHERE STRFTIME('%Y-%m-%d','now','-1 year') = STRFTIME('%Y-%m-%d',my_dt);

SELECT * from table_dt WHERE STRFTIME('%Y-%m-%d','now','-1 month') = STRFTIME('%Y-%m-%d',my_dt);

Matching Weekday of the year

SELECT * FROM table_dt WHERE STRFTIME('%W','now')=STRFTIME('%W',my_dt);

SELECT * FROM table_dt WHERE STRFTIME('%W-%Y','now')=STRFTIME('%W-%Y',my_dt);

SELECT * FROM table_dt WHERE STRFTIME('%W-%Y','now','weekday 1')=STRFTIME('%W-%Y',my_dt);

SELECT * FROM table_dt WHERE STRFTIME('%Y-%m-%w','now')=STRFTIME('%Y-%m-%w',my_dt);

SQLite Supported Date and Time Functions

SQLite provides several built-in functions to work with date and time:

• DATE(): Returns the date in YYYY-MM-DD format.

SELECT DATE('now');

• TIME(): Returns the time in HH:MM:SS format.

SELECT TIME('now');

• DATETIME(): Returns the date and time in YYYY-MM-DD HH:MM:SS format.

SELECT DATETIME('now');

• JULIANDAY(): Returns the Julian day number.

SELECT JULIANDAY('now');

• STRFTIME(): Formats the date/time based on a format string.

SELECT STRFTIME('%Y-%m-%d %H:%M:%S', 'now');

Formatting Options Using STRFTIME() in SQLite