Android Question Date Function

sueb · Jun 28, 2014

I HAVE A DATABASE WITH DATE AND OTHER DATA IN IT
NOW I WANT TO EXTRACT DATE YEAR WISE,MONTH WISE,DAY WISE.

PLEASE CORRECT THE STATEMATE FOR EXTRACTING YEAR WISE (ANY YEAR)
Cursor1 = SQL1.ExecQuery("SELECT col1, col2, col3, col4 FROM table2 WHERE col1 = year 2013 ")
PLEASE SUGGEST STATEMATE FOR EXTRACTING YEAR AND MONTH WISE
PLEASE SUGGEST STATEMATE FOR EXTRACTING YEAR AND MONTH AND DAY WISE

KMatle · Jun 28, 2014

If col1 is has the format "date":

Select .... from table2 where col1 >= "2013-01-01" and col1 <="2013-12-31"

or

Select ... from table2 where YEAR(col1) = 2013

You can add month, too:

Select ... from table2 where YEAR(col1) = 2013 and month(col1) = 5

sueb · Jun 28, 2014

Klaus Matle said:
If col1 is has the format "date":

Select .... from table2 where col1 >= "2013-01-01" and col1 <="2013-12-31"

or

Select ... from table2 where YEAR(col1) = 2013

You can add month, too:

Select ... from table2 where YEAR(col1) = 2013 and month(col1) = 5

I AM GETTING AN ERROR SHOWING THIS
LastException android.database.sqlite.SQLiteException:no such function:YEAR:,while compiling:SELECT col1,col2,col3,col4 FROM table2 WHERE YEAR(col1) = 2013

Mahares · Jun 28, 2014

Suppose your column col1 is formatted like this: 2013-12-31, you need to use the Substr function:

B4X:

Cursor1 = SQL1.ExecQuery("SELECT col1, col2, col3, col4 , Substr(col1,1,4) AS YR FROM table2 WHERE YR= '2013' ")
Cursor1 = SQL1.ExecQuery("SELECT col1, col2, col3, col4 , Substr(col1,6,2) AS MO FROM table2 WHERE MO= '12' ")
Cursor1 = SQL1.ExecQuery("SELECT col1, col2, col3, col4 , Substr(col1,9,2) AS DA FROM table2 WHERE DA= '31' ")

possibly this too:

B4X:

Cursor1 = SQL1.ExecQuery("SELECT col1, col2, col3, col4 FROM table2 WHERE Substr(col1,1,4) = '2013' ")
Cursor1 = SQL1.ExecQuery("SELECT col1, col2, col3, col4 FROM table2 WHERE Substr(col1,6,2) = '12' ")
Cursor1 = SQL1.ExecQuery("SELECT col1, col2, col3, col4 FROM table2 WHERE Substr(col1,9,2) = '31' ")

KMatle · Jun 29, 2014

Sorry. I didn't see it was SqlLite...

Android Question Date Function

sueb

New Member

KMatle

Expert

sueb

New Member

Mahares

Expert

KMatle

Expert

Similar Threads

Android Question Date Function

sueb

New Member

KMatle

Expert

sueb

New Member

Mahares

Expert

KMatle

Expert

Similar Threads

Privacy & Transparency

Privacy & Transparency