CODEWITHSUNDEEP
androidsqlitedate
Maheedhar Pamuru
Maheedhar Pamuru

Reputation: 149

How get date from datetime field in Sqlite ( android )

In my app I am storing the creation date and time (of a task) as datetime field in the database.

In one of the activities I am trying to display all the list of tasks created, and I need Creation date (just date not datetime) as my first column.

I have used a listview and simple cursor adapater to populate the listview.

private String [] from = { "CREATION_DATE" ,  "_id" , DataBaseHandler.DESCRIPTION };
private int[] to ={R.id.fh_list_entryDate ,R.id.fh_list_credits, R.id.fh_desc };
//date string will be in yyyy/mm/dd format.
fh_cursor= dbHandler.getAllHistory(bun.getString("st_date"),bun.getString("end_date")); 
dataAdapter = new SimpleCursorAdapter(this,R.layout.full_history_list,fh_cursor,from,to,0);
listView.setAdapter(dataAdapter);

the xml for the "fh_list_entryDate"

<EditText
        android:id="@+id/fh_list_entryDate"
        android:layout_width="120dp"
        android:layout_height="wrap_content"
        android:layout_marginStart="4dp"
        android:inputType="date"
        android:focusable="false"
        android:gravity="center"
        android:textAppearance="?android:attr/textAppearanceMedium" />

in the getAllHistory method the query looks like:

String historyQuery = "SELECT CREATION_DATE , CREDITS _id , DESCRIPTION FROM "+
                    ACTIVITY_DETAILS_TABLE_NAME +
                    " WHERE START_DT > ? "+" AND END_DT < ? ";
fh_cursor = sqlDB.rawQuery(historyQuery, new String[] {st_date,end_date});

If I use the above code I am getting "Tue Jul 01 23:21:05 GMT-04:00 2014" in the first column.

I read about the "strftime" and I modified the query to:

String historyQuery = "SELECT strftime('%Y-%m-%d', CREATION_DATE) CREATION_DATE, 
                       CREDITS _id , DESCRIPTION FROM "+
                    ACTIVITY_DETAILS_TABLE_NAME +
                    " WHERE START_DT > ? "+" AND END_DT < ? ";

Then am getting null instead of date.

My requirement is to display only the date part. preferably in yyyy/mm/dd format. or yyyy-mm-dd format.

I guess one option is instead of using SimpleCursorAdapter we can iterate over the fh_cursor and coavert the datetime to desrired format using SimpleDateFormat in java code. I dont want to do that. Is there a way I can get this my just modifying the query?

Upvotes: 2

Views: 10898

Answers (2)

donison24x7
donison24x7

Reputation: 304

You should note that there is NO any DATETIME datatype in sqlite. Instead of it use NUMERIC datatype to store date. And before inserting date into the table convert that date to longmillis using SimpleDateFormat.

long date = SimpleDateFormat("yyyy-MM-dd").parse("your-date-text").getTime();

now when when getting date from the table, iterate through the cursor and get datelongmillis as long

long datemillis = cursor.getLong(cursor.getColumnIndex("date_column_name"));

now its time to display date in your format, use again SimplaDateFormat.

String dateString = new SimpleDateFormat("yyyy-MM-dd").format(new Date(datemillis));

Upvotes: 0

ngrashia
ngrashia

Reputation: 9904

Try using strftime in the WHERE clause as below:

String historyQuery = "SELECT strftime('%Y-%m-%d', CREATION_DATE) CREATION_DATE, 
                       CREDITS _id , DESCRIPTION FROM "+
                     " ACTIVITY_DETAILS_TABLE_NAME "+
                     " WHERE strftime('%Y-%m-%d',START_DT) > ? "+
                     " AND strftime('%Y-%m-%d',END_DT) < ? ";

Upvotes: 1

Related Questions