CODEWITHSUNDEEP
sqlite
Bryan Bell
Bryan Bell

Reputation: 27

How to filter data between date & time in sqlite

I have a table Orders with Order_Date datatype is smalldatetime and my Order_Date Format is 01/10/2018 10:00:00 PM

Now I want to filter data between 01/10/2018 04:00:00 PM AND 02/10/2018 04:00:00 AM

What I tried

SELECT distinct(Order_No),Order_Date from Orders WHERE Order_Date BETWEEN '01/10/2018 04:00:00 PM' and '02/10/2018 04:00:00 AM'

This query is showing only 01/10/2018 Data but I want the data BETWEEN 01/10/2018 04:00:00 PM and 02/10/2018 04:00:00 AM

Is there any way to get the data from today 4PM To Next Day 4AM?

Upvotes: 1

Views: 1360

Answers (1)

Shawn
Shawn

Reputation: 52409

First off, sqlite does not have actual date/time types. It's a simple database with only a few types. Your smalldatetime column actually has NUMERIC affinity (See the affinity rules).

For Sqlite's builtin functions to be able to understand them, date and times can be stored as numbers or text; numbers are either the number of seconds since the Unix epoch, or a Julian day. Text strings can be one of a number of formats; see the list in the docmentation. All these have the additional advantage that, when compared to other timestamps in the same format, they can be properly sorted.

You seem to be using text strings like '01/10/2018 04:00:00 PM'. This is not one of the formats that sqlite date and time functions understand, and it doesn't sort naturally, so you can't use it in comparisons aside from testing equality. Plus it's ambiguous: Is it October 1, or January 10? Depending on where you're from you'll have a different interpretation of it.

If you change your timestamp format to a better one like (Assuming October 1) '2018-10-01 16:00:00', you'll be able to sort and compare ranges, and use it with sqlite functions.

Upvotes: 1

Related Questions