tags:

views:

19

answers:

1
Q: 

How to convert TEXT data to DATE TIME in SQLITE in Android?

Hi!

I am new to android. I am using a database to store my data. I also store date as TEXT in database. format of date is as follow... "Mon, 20 Oct 2010 12:30:00" So i stored them in TEXT datatype not in DateTime. Now i want to display them in following format "Mon, 20 Oct 2010" The main thing is that i want to ORDER them in DESC. I write a query for this but it dose not work. It there is some alternative for this???? i also read about "strftime()" function of SQLITE but i did not know how to use it for this.

My Query is:

"Cursor c = myDB.rawQuery("Select * from List ORDER BY to_date(substr(date,6,20), 'DD-MM-YYYYTHH24:MI:SS') DESC",null);"

Please Help me... Thanks in advance!

+1  A: 

I'd recommend you to store your DateTime values as INTEGER using Date.getTime() (milliseconds since Jan. 1, 1970, midnight GMT). So you can use ASC and DESC operators without conversions which will require less resources.

cement  2010-10-25 07:20:35
So in such a case i have to store my date in 2 columns. It is right?
Arslan  2010-10-25 07:50:37
Can any function of sqlite is not going to help me????
Arslan  2010-10-25 07:51:14
Why do you need 2 columns? 1 will be enough.
cement  2010-10-25 08:01:18
But i am storing whole date as TEXT because need this whole date for display to user, but in such a case it must be sorted in DESC... For 1 column for whole date and 1 for TIME as INTEGER?????
Arslan  2010-10-25 08:05:02
No. Date.getTime() converts both date and time to into single integer value. Same way you can convert it back to Date after reading from database and format it as needed.
cement  2010-10-25 08:12:46
Oh! Thanks. Can you please send me a example or a useful link?
Arslan  2010-10-25 08:16:24
Date.getTime() is a java funtion? or a sqlite function?
Arslan  2010-10-25 08:16:47
java.util.Date. However I believe SQL type has similar functionality.
cement  2010-10-25 09:00:58
It is a java function that converts java.util.Date to long that can be stored as INTEGER in SQLite database.
cement  2010-10-25 15:41:29

related questions

Handling and storing elapsed time
How do I convert local time to UTC in Python?
Mono's DateTime Serialization
Best practices for DateTime serialization in .Net framework 3.5/SQL Server 2008
Parsing exact dates in C# shouldn't force you to create an IFormatProvider
C# Casting vs. Parse
.NET Date Const (with Globalization)
Dynamic regex for date time formats
What is the simplest way to find the difference between 2 times in python?
Best way to convert DateTime to "n Hours Ago" in SQL
What's a good way to overwrite DateTime.Now during testing?
Datetime arithmetic with a string in Ruby
How can I get the DateTime for the Start of the Week?
Is DateTime.Now the best way to measure a function's performance?
SQL Server DateTime conversion failure
Natural language date/time parser for .NET?
What's a good way to check if two datetimes are on the same calendar day in TSQL?
Why does this python date/time conversion seem wrong?
Dealing with PHP server and MySQL server in different time zones
How do I parse and convert DateTime's to the RFC 3339 date-time format?
HOWTO - Compare a date string to datetime in SQL Server?
Best method for varchar date validation in Sybase (T-SQL)?
What's the BEST way to remove the time portion of a datetime value (SQL Server)
How do I calculate relative time?
How do I calculate someone's age in C#?