Get Month from Datetime in SQLite

get only month from date in sqlite database

You can use the function strftime() only with the format 'YYYY-MM-DD', so either you should change the format of your dates or use the function SUBSTR() to get the month:

c.execute(f"SELECT * FROM sales WHERE substr(date, 4, 2) = '{var}'")

if you pass var padded with '0' at the left (if needed), so the comparison is alphabetical,

or:

c.execute(f"SELECT * FROM sales WHERE substr(date, 4, 2) + 0 = {var}")

so the comparison will be numerical.

In any case it is safer to write the code like this:

c.execute("SELECT * FROM sales WHERE substr(date, 4, 2) + 0 = ?", (var,))

where var is an integer variable,

or:

c.execute("SELECT * FROM sales WHERE substr(date, 4, 2) = ?", (var,))

where var is a string variable padded with '0' at the left (if needed) like your code.

SQLite SELECT date for a specific month

How about

SELECT * FROM table_name WHERE strftime('%m', date_column) = '04'

Date And Time Functions

how to extract only month from date which is in sqlite database?

SQLite only has a small set of date and time functions. You can use them like this:

sqlite> .dump
PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE t1 (f1 string);
INSERT INTO "t1" VALUES('03/31/1970');
COMMIT;

sqlite> select substr(f1, 7) || '-' || substr(f1, 0, 3) || '-' || substr(f1, 4, 2) from t1;
1970-03-31

sqlite> select strftime("%m", substr(f1, 7) || '-' || substr(f1, 0, 3) || '-' || substr(f1, 4, 2)) from t1;
03

How to extract day and month from a Date data in sqlite database

You can use string functions:

select *
from mytable
where substr(bornadte, 1, 5) = strftime('%d/%m', 'now')

Note that you should not be storing dates in this format. SQLite understands dates in format YYYY-MM-DD. I would strongly recommend fixing your data model:

update mytable
set borndate = substr(borndate, -4) 
    || '-' ||  substr(borndate, 4, 2) 
    || '-' ||  substr(borndate, 1, 2)

Using datetime in SQLite for getting values this month and last month

Computing the last day of the month is not the only issue with date/datetime values generally.

If you have a value on the last day of the month that was posted at noon. It will not be counted if you compare just against the date without the date time.

You should do calculations like this where >= '01/01/2015 00:00:00 and where date < 02/01/2015 00:00:00. That way all of the 1/31/2015 records are counted no matter what time they are time stamped at. Or you can strip out the Month, day and year ignoring the time '20150101' and '20150131' as character strings on your comparisons.

How to retrieve the Date part out of a Datetime result column in SQLite?

You can use the DATE function.

Example

> select date('2011-04-26 18:40:34')
> 2011-04-26

You can get only the day with strftime,

> select strftime('%d', '2011-04-26 18:40:34')
> 26

SQLite query for n-th day of month

Assuming that the date values do not use the format dd.mm.yyyy but one of the supported date formats, you can use the built-in date functions to compute this.

To compute the difference, in days, between two dates, convert them into a date format that uses days as a number, i.e., Julian days.

To get the 'base' day for a month, we can use modifiers:

> SELECT julianday('2001-02-11') -
         julianday('2001-02-11', 'start of month', '+10 days') + 2;
2.0

(The +2 is needed because we add to the 1st of the month, not the 0th, and we count beginning at 1, not 0.)

If the day is before the tenth, the computed value would become zero or negative, and we have to use the previous month instead:

> SELECT julianday('2001-02-09') -
         julianday('2001-02-09', 'start of month', '-1 month', '+10 days') + 2;
31.0

Combining these results in this expression to compute the n for a date Date:

CASE
WHEN julianday(Date) -
     julianday(Date, 'start of month', '+10 days') + 2 > 0
THEN julianday(Date) -
     julianday(Date, 'start of month', '+10 days') + 2
ELSE julianday(Date) -
     julianday(Date, 'start of month', '-1 month', '+10 days') + 2
END

You can the use this in your query:

SELECT CASE...END AS Day,
       AVG(Value) AS Average
FROM Data
GROUP BY Day;