unable to form SQL

Question

I have orders table in which orders are stored based on order received datetime(timestamp).

Now, I want only the orders between 2 dates, and that too, for everyday in those dates, between 2 and 4 PM only.

How can I achieve this, preferably in a single SQL query?

http://stackoverflow.com/questions/2930768/how-to-compare-sqlite-timestamp-values — Habib, May 09 '12 at 07:54
@MiikaL. : frankly sayimg, I'm totally clueless about how to achieve this in one query, although i have already done it using loops by first fetching between days, and then for each day, between specified times — Nitin Bansal, May 09 '12 at 07:55
@Habib.OSU : sory dear, but that's not related to the question i posted — Nitin Bansal, May 09 '12 at 07:56

score 2 · Accepted Answer · answered May 09 '12 at 08:05

I am not absolutely certain of the SQLLite syntax, but something along the lines of the following might get you on the right track:

SELECT *
FROM [orders]
WHERE orderReceived >= @startDate
    AND orderReceived < @endDate
    AND (strftime("%H", orderReceived) = "14"
        OR strftime("%H", orderReceived) = "15")

score 1 · Answer 2 · answered May 09 '12 at 08:04

1

Add the following to you where clause:

time(mytimecol) > '14:00:00' AND time(mytimecol) < '16:00:00'

CF date and time functions in SQLite.

answered May 09 '12 at 08:04

McGarnagle

101,349
31
229
260

unable to form SQL

2 Answers2