Problem executing android SQLITE query (alternative of SQL query)

Question

I am trying to execute the android SQLite query (alternative SQL query) for my project. When I execute the alternative query, an error is generated. How can I solve this problem? Any help will be appreciated.

Table Structure

TABLE (
id                     VARCHAR(250) NULL
,lastUpdateDate         VARCHAR(500) NULL
,createdTime            VARCHAR(500) NULL
,takenDate              LONG NULL
,endDate                LONG NULL
)

DECLARE @SelecctedDate DATE ='2022-11-30'

SQL Query

SELECT *, dateadd(s, convert(bigint, takenDate) / 1000, convert(datetime, '1970-1-1 00:00:00')) AS startdate,
 dateadd(s, convert(bigint, endDate) / 1000, convert(datetime, '1970-1-1 00:00:00')) AS enddate FROM @Table
WHERE (
        ((@SelecctedDate>= dateadd(s, convert(bigint, takenDate) / 1000, convert(datetime, '1970-1-1 00:00:00')) AND takenDate != '0' )

            AND (@SelecctedDate <= dateadd(s, convert(bigint, endDate) / 1000, convert(datetime, '1970-1-1 00:00:00')) AND endDate != '0'))

        OR ((@SelecctedDate= dateadd(s, convert(bigint, takenDate) / 1000, convert(datetime, '1970-1-1 00:00:00')) AND takenDate != '0' )AND endDate='0')

        OR (takenDate ='0' AND (@SelecctedDate = dateadd(s, convert(bigint, endDate) / 1000, convert(datetime, '1970-1-1 00:00:00')) AND endDate != '0'))

        OR (takenDate ='0' AND endDate='0' AND (@SelecctedDate = dateadd(s, convert(bigint, createdTime) / 1000, convert(datetime, '1970-1-1 00:00:00')) ))

        OR @SelecctedDate IS NULL
)

Alternate of SQL Query (Android SQLITE SQLite)

SELECT * FROM $table 
WHERE (
    (($selectedDate >= strftime('%Y-%m-%d', takenDate/1000, 'unixepoch') AND takenDate != '0')

        AND ($selectedDate <= strftime('%Y-%m-%d', endDate/1000, 'unixepoch') AND endDate != '0'))

        OR (($selectedDate = strftime('%Y-%m-%d', takenDate/1000, 'unixepoch') AND takenDate != '0') AND endDate='0')

        OR (takenDate ='0' AND ($selectedDate = strftime('%Y-%m-%d', endDate/1000, 'unixepoch') AND endDate != '0'))

        OR (takenDate ='0' AND endDate='0' ($selectedDate = strftime('%Y-%m-%d', lastUpdateDate/1000, 'unixepoch' )))

    )

When I run SQLite raw query I got an issue:

database.SQLiteException: near "(": syntax error: , while compiling: SELECT * FROM medication WHERE ((( 2022-11-29 >= strftime('%Y-%m-%d', takenDate/1000.0, 'unixepoch') AND takenDate != '0') AND ( 2022-11-29 <= strftime('%Y-%m-%d', endDate/1000, 'unixepoch') AND endDate != '0')) OR (( 2022-11-29 = strftime('%Y-%m-%d', takenDate/1000, 'unixepoch') AND takenDate != '0') AND endDate='0') OR (takenDate ='0' AND ( 2022-11-29 = strftime('%Y-%m-%d', endDate/1000, 'unixepoch') AND endDate != '0')) OR (takenDate ='0' AND endDate='0' ( 2022-11-29 = strftime('%Y-%m-%d', lastUpdateDate/1000, 'unixepoch'))))

PChemGuy · Accepted Answer · 2022-11-29 08:56:41Z

1

Missing operator after OR (takenDate ='0' AND endDate='0', perhaps AND:

SELECT * FROM $table 
WHERE (
    (($selectedDate >= strftime('%Y-%m-%d', takenDate/1000, 'unixepoch') AND takenDate != '0')

        AND ($selectedDate <= strftime('%Y-%m-%d', endDate/1000, 'unixepoch') AND endDate != '0'))

        OR (($selectedDate = strftime('%Y-%m-%d', takenDate/1000, 'unixepoch') AND takenDate != '0') AND endDate='0')

        OR (takenDate ='0' AND ($selectedDate = strftime('%Y-%m-%d', endDate/1000, 'unixepoch') AND endDate != '0'))

        OR (takenDate ='0' AND endDate='0' AND ($selectedDate = strftime('%Y-%m-%d', lastUpdateDate/1000, 'unixepoch' )))

    )

answered Nov 29, 2022 at 8:56

PChemGuy

1,8593 gold badges10 silver badges23 bronze badges

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

1 Comment

Rabindra Acharya Over a year ago

Thank you for your answer. My next question: Is this the correct way to convert millisecond value to date format in android sqlite? strftime('%Y-%m-%d', takenDate/1000, 'unixepoch') Scenario: User select date is in format 2012-04-28 and takendate has a milliseconds value on the database. I used the above method to convert the milliseconds to date format(yyyy-MM-dd).

Collectives™ on Stack Overflow

Problem executing android SQLITE query (alternative of SQL query)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related