Sqlite Select with Condition on Date

sqlite select with condition on date

Some thing like this could be used:

select dateofbirth from customer Where DateofBirth  BETWEEN date('1004-01-01') AND date('1980-12-31');  
select dateofbirth from customer where date(dateofbirth)>date('1980-12-01');
select * from customer where date(dateofbirth) < date('now','-30 years');

If you are using Sqlite V3.7.12 or greater

Dont use date(dateofbirth) just use dateofbirth. So your query would look like this:

select * from customer where dateofbirth < date('now','-30 years');

How to select rows by date in sqlite

Firstly, format your dates to the ISO-8601 standard. Wrap it in Date() to ensure it gets processed as a DATE. Finally, construct your range so that it will include everything from 12:00am onwards until just before 12:00am the next day.

select itemId, dateColumn
from items
where dateColumn >= date('2012-10-23')
  AND dateColumn <  date('2012-10-23', '+1 day')

SQLite columns are not typed. However, if you compare the column to a DATE as shown, it is sufficient to coerced the column data into dates (null if not coercible) and the comparison will work properly.

Example on SQLFiddle:

create table items (
  itemid, datecolumn);
insert into items select
  1,'abc' union all select
  2,null union all select
  3,'10/23/2012 12:23' union all select
  4,'10/23/2012' union all select
  5,'2012-10-23 12:23' union all select
  6,'2012-10-23' union all select
  7,'2012-10-24 12:23' union all select
  8,'2012-10-24' union all select
  9,date('2012-10-24 12:23') union all select
  10,date('2012-10-24');

Results:

itemid  datecolumn
5       2012-10-23 12:23
6       2012-10-23

Note that although rows 3 and 4 appear to be dates, they are not, because they do not conform to ISO-8601 formatting which is the only format recognized by SQLite.

sqlite condition SELECT on datetime

Store timestamp value as unixepoch, this makes comparison bit easy:

Have a look at the following Example:

Create Table:

create table measurements(id INTEGER PRIMARY KEY AUTOINCREMENT, LOCAL_TIME TIMESTAMP);

Insert Data:

insert into measurements(LOCAL_TIME) values(strftime('%s', '2013-12-16 00:00:00'));
insert into measurements(LOCAL_TIME) values(strftime('%s', '2013-12-16 00:20:00'));
insert into measurements(LOCAL_TIME) values(strftime('%s', '2013-12-17 00:00:00'));
insert into measurements(LOCAL_TIME) values(strftime('%s', '2013-12-17 00:20:00'));

Query "SELECT only those rows where the LOCAL_TIME is midnight (so 00:00:00 of every day)"

select datetime(LOCAL_TIME, 'unixepoch') from measurements where LOCAL_TIME - strftime("%s", date(LOCAL_TIME, 'unixepoch'))=0;

Simple idea: if datetime-date = 0 we select the row

Output:

2013-12-16 00:00:00
2013-12-17 00:00:00

Link for sqlite date,time functions: http://www.tutorialspoint.com/sqlite/sqlite_date_time.htm

SQLite to select dates within a range but without Sundays and some holidays

Part of your issue is that 'weekday' (a string) will always be greater than 0, so that test will always be true.

The second issue is that recursion halts when an empty result is returned from the right(recursive) SELECT. So if you had the test for a Sunday in this select then no more recursions will be made when the condition is not met. Thus, only data up until the first Sunday (Sunday would result in no row) would be selected (as 2017-09-01 is a Friday, you would get just 2 rows as the 3rd is a Sunday). Therefore the test for Sunday should be after the recursion.

What you want is to get the day of the week as an integer 0-6 (Sun-Sat). You can use the strftime('%w',valid_date) to get the value, but you then need to ensure that is is an integer rather than text/char/string so you could use CAST(strftime('%w',valid_date) As INTEGER).

To fix the rows limited to just up till the first Sunday, the WHERE clause for removing Sundays should be in the final SELECT after the recursion.

As such, the following is a fix for the issues that you could use :-

WITH RECURSIVE dates(x) AS ( 
            SELECT '2017-09-01'
                UNION ALL 
            SELECT DATE(x, '+1 DAYS') FROM dates WHERE x <'2017-09-30'
        ) 
        SELECT x FROM dates WHERE CAST(strftime('%w',x) As INTEGER) > 0;

This would result in 26 rows, instead of the 30 e.g. :-

......

• i.e. the 3rd and 10th are excluded (and so on).

Additional

re the additional question :-

Also I would like to exclude some dates from the result set of dates
which dates are in another table in the database. Let is call that
table Holidays. The schema for that table could be like this:

CREATE TABLE Holidays (
    id              INTEGER PRIMARY KEY
                            UNIQUE,
    NameOfHoliday      TEXT    DEFAULT NULL,
    startDate DATE    DEFAULT NULL,
    endDate DATE    DEFAULT NULL
);

Let insert some data into Holidays table:

INSERT INTO Holidays VALUES(1,'Winter Break','2017-12-23','2018-01-14');

This is quite complicated especially as you could potentially have multiple holidays to consider (say you wanted to do a whole year). What I would suggest is rather than have a range of dates in two columns is to have a table with individual dates that are holidays, this is then very simple to cater for.

Instead of the Holidays table lets say there is the simpler AltHolidays table e.g. :-

CREATE TABLE IF NOT EXISTS AltHolidays (NameOfHoliday TEXT, Day_To_Exclude TEXT);
INSERT OR IGNORE INTO AltHolidays VALUES
    ('Winter Break','2017-12-23'),
    ('Winter Break','2017-12-24'),
    ('Winter Break','2017-12-25'),
    ('Winter Break','2017-12-26'),
    ('Winter Break','2017-12-27'),
    ('Winter Break','2017-12-28'),
    ('Winter Break','2017-12-29'),
    ('Winter Break','2017-12-30'),
    ('Winter Break','2017-12-31'),
    ('Winter Break','2018-01-01'),
    ('Winter Break','2018-01-02'),
    ('Winter Break','2018-01-03'),
    ('Winter Break','2018-01-04'),
    ('Winter Break','2018-01-05'),
    ('Winter Break','2018-01-06'),
    ('Winter Break','2018-01-07'),
    ('Winter Break','2018-01-08'),
    ('Winter Break','2018-01-09'),
    ('Winter Break','2018-01-10'),
    ('Winter Break','2018-01-11'),
    ('Winter Break','2018-01-12'),
    ('Winter Break','2018-01-13'),
    ('Winter Break','2018-01-14')
;

• Ideally descriptions would be held in it's own table and it would be referenced.

Then (assuming you still want to exclude Sundays as well) you could use NOT IN as e.g. :-

WITH RECURSIVE dates(x) AS ( 
            SELECT '2017-11-01'
                UNION ALL 
            SELECT DATE(x, '+1 DAYS') FROM dates WHERE x<'2018-01-31'
        ) 
        SELECT * 
                FROM dates 
                WHERE x NOT IN (SELECT Day_To_Exclude FROM AltHolidays) -- Exclude holidays
                    AND CAST(strftime('%w',x) AS INTEGER) <> 0 -- Exclude Sundays
;

This would result in 60 rows (92 - 23 (holidays) - 9 (remaining Sundays))

SQLite database - select the data between two dates?

You could also just not use between.

select * from mytable where `date` >= '2014-10-09' and `date` <= '2014-10-10'

Example:

mysql> create table dd (id integer primary key auto_increment, date text);
Query OK, 0 rows affected (0.11 sec)

mysql> insert into dd(date) values ('2014-10-08'), ('2014-10-09'), ('2014-10-10'), ('2014-10-11');
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> select * from dd where date >= "2014-10-09" and date <= "2014-10-10";
+----+------------+
| id | date       |
+----+------------+
|  2 | 2014-10-09 |
|  3 | 2014-10-10 |
+----+------------+
2 rows in set (0.01 sec)

Since it includes time, and you dont want the time. this:

select substring(date, 1, 10) from dd where substring(date, 1, 10) between '2014-10-09' and '2014-10-10';

question updated again, additional answer

Ugh. you have timestamp fields? in that case this:

select date(from_unixtime(timestamp)) from mytabel where date(from_unixtime(timestamp)) between '2014-10-09' and '2014-10-10'

finally we have arrived at sqlite

select date(datetime(timestamp, 'unixepoch')) 
  from mytable 
    where date(datetime(timestamp, 'unixepoch')) 
      between '2014-10-09' and '2014-10-10';

SQL Select between dates

SQLite requires dates to be in YYYY-MM-DD format. Since the data in your database and the string in your query isn't in that format, it is probably treating your "dates" as strings.

How do you select the most recent date from a database table in SQLite?

Thanks to forpas for the response. This code worked with the suggested edit of changing the dates format in the table to yyyy-MM-dd:

cmd.CommandText = @"SELECT * FROM account AS a WHERE created = (SELECT MAX(created) FROM account AS b WHERE custid = @custid)";
                cmd.Parameters.AddWithValue("custid", Global.selectedCust);
                SQLiteDataReader read = cmd.ExecuteReader();
                while (read.Read())
                {
                    newestDate = read["created"].ToString();
                }

---

Related Topics