Android Question Date Function

[sueb]

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]

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]

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]

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

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:

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]

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