Select between 2 dates in sqlite, using 2 columns

Question

I have an table RECORDS in SQLITE where it contains 2 columns date varchar, time varchar, where date column is in format 2017-01-20 and time format 15:43

CREATE TABLE "RECORDS" (
  "date" VARCHAR,  /*2017-01-23*/
  "time" VARCHAR    /*14:00*/
  )

Is there a way to select all the records from the table Between 2017-01-23 05:00 AND 2017-01-24 02:00

Here is the sql Fiddle

I tried (

SELECT * RECORDS
WHERE date >= date('2017-01-23') AND date <= date('2017-01-24')
ORDER BY DATE,TIME

but I can't select also the 05:00 - 02:00

Tim Biegeleisen · Accepted Answer · 2017-01-23 13:46:38Z

1

I think the following query should work, because comparisons against the date and time should work numerically given the formats you have (i.e. yyyy-mm-dd for dates, hh:mm for times).

SELECT *
FROM RECORDS
WHERE (date = '2017-01-23' AND time > '05:00') OR
      (date = '2017-01-24' AND time < '02:00')

This assumes that all entries in your time column have five characters in the format hh:mm.

answered Jan 23, 2017 at 13:46

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Select between 2 dates in sqlite, using 2 columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related