androidandroid-sqlite
data not getting when calling date between
my table structure are as
CREATE TABLE "acc_transaction" (
"ID" INTEGER NOT NULL,
"VNo" TEXT DEFAULT NULL,
"Vtype" TEXT DEFAULT NULL,
"VDate" date DEFAULT NULL,
"COAID" TEXT NOT NULL,
"Narration" text DEFAULT NULL,
"Debit" decimal(18, 2) DEFAULT NULL,
"Credit" decimal(18, 2) DEFAULT NULL,
"IsPosted" char(10) DEFAULT NULL,
"is_opening" INTEGER NOT NULL DEFAULT 0,
"company_id" INTEGER NOT NULL,
"CreateBy" TEXT DEFAULT NULL,
"CreateDate" datetime DEFAULT NULL,
"UpdateBy" TEXT DEFAULT NULL,
"UpdateDate" datetime DEFAULT NULL,
"IsAppove" char(10) DEFAULT NULL,
PRIMARY KEY("ID" AUTOINCREMENT)
);
and my query are as
String sql = "SELECT * FROM acc_transaction WHERE company_id ="
+ company_id
+ " AND COAID =" + bankHeadCode
+ " AND VDate >= Date('" + fromDate + "') AND VDate <= Date('" + toDate + "')";
but i am getting empty result. please guide me i am making a mistake please let me know.
Solution
You issue is very much that the dates are not comparable as they are and hence why you would get unexpected results.
You create table statement indicates that you have perhaps used a different database and expect that a column type of datetime handles dates. SQLite does not have a datetime type. Rather dates can be stored in a multitude of ways, but many user friendly formats are not suitable for comparison or for sorting.
The VDate column from you screen shot as various formats. Furthermore the from and to dates used for selection have not been shown. Additionally you would have issues with + " AND COAID =" + bankHeadCode, if bankHeadCode is not enclosed within single quotation marks within the variable.
Assuming that the issue is solely with the numerous date formats and that you use a date in the format yyyy-mm-dd (e.g. 2021-01-01 NOT 2021-1-1 NOR 2021-1-01) then the following is a solution that converts the Vdate column into a converted_vdate column by the means of a Common Table Expression allowing the date selection to be applied with predictable and expected results.
The working query (heed the comments) :-
WITH cte_converted_vdate AS (
SELECT *,
CASE
/* d-m-yyyy */
WHEN substr(vdate,2,1) = '-' AND substr(vdate,4,1) = '-'
THEN substr(vdate,5)||'-0'||substr(vdate,3,1)||'-0'||substr(vdate,1,1)
/* d-mm-yyyy */
WHEN substr(vdate,2,1) = '-' AND substr(vdate,5,1) = '-'
THEN substr(vdate,6)||'-'||substr(vdate,3,2)||'-0'||substr(vdate,1,1)
/* dd-m-yyyy */
WHEN substr(vdate,3,1) = '-' AND substr(vdate,5,1) = '-'
THEN substr(vdate,6)||'-0'||substr(vdate,4,1)||'-'||substr(vdate,1,2)
/* dd-m-yyyy */
WHEN substr(vdate,3,1) = '-' AND substr(vdate,6,1) = '-'
THEN substr(vdate,7)||'-'||substr(vdate,4,2)||'-'||substr(vdate,1,2)
/* yyyy-m-d */
WHEN substr(vdate,5,1) = '-' AND substr(vdate,7,1) = '-' AND length(vdate) = 8
THEN substr(vdate,1,5)||'0'||substr(vdate,6,1)||'-0'||substr(vdate,8,1)
/* yyyy-m-dd */
WHEN substr(vdate,5,1) = '-' AND substr(vdate,7,1) = '-' AND length(vdate) = 9
THEN substr(vdate,1,5)||'0'||substr(vdate,6,1)||'-'||substr(vdate,8,2)
/* yyyy-mm-d */
WHEN substr(vdate,5,1) = '-' AND substr(vdate,8,1) = '-' AND length(vdate) = 9
THEN substr(vdate,1,8)||'0'||substr(vdate,9,1)
/* yyyy-mm-dd */
WHEN substr(vdate,5,1) = '-' AND substr(vdate,8,1) = '-' AND length(vdate) = 10
THEN vdate
ELSE 'not yet catered for'
END AS converted_vdate
FROM acc_transaction
)
SELECT *
FROM cte_converted_vdate
WHERE company_id = 1 /*<<<<<<<<<< change as required */
AND COAID = coaid /*<<<<<<<<<< change as required */ /*NOTE set to always be true for testing */
AND converted_vdate BETWEEN '2021-10-13' /*<<<<<<<<< fromdate change as required */ AND '2021-10-14' /*<<<<<<<<< todate change as required*/
;
Using the following data (as per screenshot but with some additional data) :-
Running the above results in :-
The above was tested using the following:-
DROP TABLE IF EXISTS acc_transaction;
CREATE TABLE IF NOT EXISTS acc_transaction (
ID INTEGER NOT NULL,
VNo TEXT DEFAULT NULL,
Vtype TEXT DEFAULT NULL,
VDate date DEFAULT NULL,
COAID TEXT NOT NULL,
Narration text DEFAULT NULL,
Debit decimal(18, 2) DEFAULT NULL,
Credit decimal(18, 2) DEFAULT NULL,
IsPosted char(10) DEFAULT NULL,
is_opening INTEGER NOT NULL DEFAULT 0,
company_id INTEGER NOT NULL,
CreateBy TEXT DEFAULT NULL,
CreateDate datetime DEFAULT NULL,
UpdateBy TEXT DEFAULT NULL,
UpdateDate datetime DEFAULT NULL,
IsAppove char(10) DEFAULT NULL,
PRIMARY KEY(ID AUTOINCREMENT)
);
INSERT INTO acc_transaction VALUES
(4,'EXTRA','Bank Transaction','1-1-2021','xxxx','blah',0,1250.00,1,0,1,'admin','25-10-2021',null,null,1),
(5,'Dc','Bank Transaction','1-10-2021','xxxx','blah',0,1250.00,1,0,1,'admin','25-10-2021',null,null,1),
(6,'Dc','Bank Transaction','1-10-2021','xxxx','blah',0,1250.00,1,0,1,'admin','25-10-2021',null,null,1),
(7,'MM','Bank Transaction','8-10-2021','xxxx','blah',0,1235.00,1,0,1,'admin','26-10-2021',null,null,1),
(8,'MM','Bank Transaction','8-10-2021','xxxx','blah',0,1235.00,1,0,1,'admin','26-10-2021',null,null,1),
(9,'Hyv','Bank Transaction','13-10-2021','xxxx','blah',0,550.00,1,0,1,'admin','26-10-2021',null,null,1),
(10,'Hyv','Bank Transaction','13-10-2021','xxxx','blah',0,550.00,1,0,1,'admin','26-10-2021',null,null,1),
(11,'123','Bank Transaction','2021-9-17','xxxx','blah',0,550.00,1,0,1,'admin','30-10-2021',null,null,1),
(12,'123','Bank Transaction','2021-9-17','xxxx','blah',0,550.00,1,0,1,'admin','30-10-2021',null,null,1),
(13,'Have','Bank Transaction','2021-10-2','xxxx','blah',1240,0.00,1,0,1,'admin','30-10-2021',null,null,1),
(14,'Have','Bank Transaction','2021-10-2','xxxx','blah',1240,0.00,1,0,1,'admin','30-10-2021',null,null,1),
(15,'Ghan','Bank Transaction','2021-10-14','xxxx','blah',786.00,0.00,1,0,1,'admin','30-10-2021',null,null,1),
(16,'Ghan','Bank Transaction','2021-10-14','xxxx','blah',786.00,0.00,1,0,1,'admin','30-10-2021',null,null,1),
(17,'But','Bank Transaction','2021-10-21','xxxx','blah',0,360.00,1,0,1,'admin','30-10-2021',null,null,1),
(18,'But','Bank Transaction','2021-10-21','xxxx','blah',0,360.00,1,0,1,'admin','30-10-2021',null,null,1),
(19,'ANOTHER','Bank Transaction','2021-1-1','xxxx','blah',0,360.00,1,0,1,'admin','30-10-2021',null,null,1)
;
SELECT * FROM acc_transaction;
/* DEMO of Date Conversion */
WITH cte_converted_vdate AS (
SELECT *,
CASE
/* d-m-yyyy */
WHEN substr(vdate,2,1) = '-' AND substr(vdate,4,1) = '-'
THEN substr(vdate,5)||'-0'||substr(vdate,3,1)||'-0'||substr(vdate,1,1)
/* d-mm-yyyy */
WHEN substr(vdate,2,1) = '-' AND substr(vdate,5,1) = '-'
THEN substr(vdate,6)||'-'||substr(vdate,3,2)||'-0'||substr(vdate,1,1)
/* dd-m-yyyy */
WHEN substr(vdate,3,1) = '-' AND substr(vdate,5,1) = '-'
THEN substr(vdate,6)||'-0'||substr(vdate,4,1)||'-'||substr(vdate,1,2)
/* dd-m-yyyy */
WHEN substr(vdate,3,1) = '-' AND substr(vdate,6,1) = '-'
THEN substr(vdate,7)||'-'||substr(vdate,4,2)||'-'||substr(vdate,1,2)
/* yyyy-m-d */
WHEN substr(vdate,5,1) = '-' AND substr(vdate,7,1) = '-' AND length(vdate) = 8
THEN substr(vdate,1,5)||'0'||substr(vdate,6,1)||'-0'||substr(vdate,8,1)
/* yyyy-m-dd */
WHEN substr(vdate,5,1) = '-' AND substr(vdate,7,1) = '-' AND length(vdate) = 9
THEN substr(vdate,1,5)||'0'||substr(vdate,6,1)||'-'||substr(vdate,8,2)
/* yyyy-mm-d */
WHEN substr(vdate,5,1) = '-' AND substr(vdate,8,1) = '-' AND length(vdate) = 9
THEN substr(vdate,1,8)||'0'||substr(vdate,9,1)
/* yyyy-mm-dd */
WHEN substr(vdate,5,1) = '-' AND substr(vdate,8,1) = '-' AND length(vdate) = 10
THEN vdate
ELSE 'not yet catered for'
END AS converted_vdate
FROM acc_transaction
)
SELECT * FROM cte_converted_vdate
;
/* Actual SQL */
WITH cte_converted_vdate AS (
SELECT *,
CASE
/* d-m-yyyy */
WHEN substr(vdate,2,1) = '-' AND substr(vdate,4,1) = '-'
THEN substr(vdate,5)||'-0'||substr(vdate,3,1)||'-0'||substr(vdate,1,1)
/* d-mm-yyyy */
WHEN substr(vdate,2,1) = '-' AND substr(vdate,5,1) = '-'
THEN substr(vdate,6)||'-'||substr(vdate,3,2)||'-0'||substr(vdate,1,1)
/* dd-m-yyyy */
WHEN substr(vdate,3,1) = '-' AND substr(vdate,5,1) = '-'
THEN substr(vdate,6)||'-0'||substr(vdate,4,1)||'-'||substr(vdate,1,2)
/* dd-m-yyyy */
WHEN substr(vdate,3,1) = '-' AND substr(vdate,6,1) = '-'
THEN substr(vdate,7)||'-'||substr(vdate,4,2)||'-'||substr(vdate,1,2)
/* yyyy-m-d */
WHEN substr(vdate,5,1) = '-' AND substr(vdate,7,1) = '-' AND length(vdate) = 8
THEN substr(vdate,1,5)||'0'||substr(vdate,6,1)||'-0'||substr(vdate,8,1)
/* yyyy-m-dd */
WHEN substr(vdate,5,1) = '-' AND substr(vdate,7,1) = '-' AND length(vdate) = 9
THEN substr(vdate,1,5)||'0'||substr(vdate,6,1)||'-'||substr(vdate,8,2)
/* yyyy-mm-d */
WHEN substr(vdate,5,1) = '-' AND substr(vdate,8,1) = '-' AND length(vdate) = 9
THEN substr(vdate,1,8)||'0'||substr(vdate,9,1)
/* yyyy-mm-dd */
WHEN substr(vdate,5,1) = '-' AND substr(vdate,8,1) = '-' AND length(vdate) = 10
THEN vdate
ELSE 'not yet catered for'
END AS converted_vdate
FROM acc_transaction
)
SELECT *
FROM cte_converted_vdate
WHERE company_id = 1 /*<<<<<<<<<< change as required */
AND COAID = coaid /*<<<<<<<<<< change as required */ /*NOTE set to always be true for testing */
AND converted_vdate BETWEEN '2021-10-13' /*<<<<<<<<< fromdate change as required */ AND '2021-10-14' /*<<<<<<<<< todate change as required*/
;
/* Cleanup */
DROP TABLE IF EXISTS acc_transaction;