Hello Everyone, I have to select all rows from database by just passing only date. For example to get all rows that I am passing date only this format: 11/9/2016 In sqlite db I store this in DATETIME format column: Eg: 11/9/2016 6:44:46 PM I have tried to get by using date() but I get nothing for date: sql = "SELECT * FROM Tble_UserSetUp inner join Tble_Login on Tble_UserSetUp.Id = Tble_Login.UserId WHERE Tble_Login.UserId='" + UserID + "' and Tble_Login.LogInTime = date('" + AccessDate + "') or Tble_Login.LogOutTime = date('" + AccessDate + "')" ; So all I need is to compare only dates but can't find how to do this in sqlite. But it return null data.

Hello Abhilash, Did you try what I suggested on another post? I think there huge amount of confusion with respect to date data-type within developer community. The statement like " In sqlite db I store this in DATETIME format column: Eg: 11/9/2016 6:44:46 PM " is inherently incorrect. Because dates are stored as integers in db, they are only SHOWN to you in this format. Database systems don't understand any date format. They format dates as per regional settings of the computer on which the client is running and show the dates. While interacting with db systems, always use a format which cannot be confused and that is yyyy-MM-dd, which is 2016-11-09. Hope it clarifies.

Answer

How to select items with respect to date only in sqlite?

Abhilash J A

692

Hello Everyone,

I have to select all rows from database by just passing only date. For example to get all rows that I am passing date only this format: 11/9/2016

In sqlite db I store this in DATETIME format column: Eg: 11/9/2016 6:44:46 PM

I have tried to get by using date() but I get nothing for date:

sql = "SELECT * FROM Tble_UserSetUp inner join Tble_Login on Tble_UserSetUp.Id = Tble_Login.UserId WHERE Tble_Login.UserId='" + UserID + "' and Tble_Login.LogInTime = date('" + AccessDate + "') or Tble_Login.LogOutTime = date('" + AccessDate + "')";

So all I need is to compare only dates but can't find how to do this in sqlite. But it return null data.