SQLite Query Questions

Question

2 questions:

First, in MSSQL, I can convert nvarchar to datetime by doing

cast('5/31/2011 12:00:00 AM' as datetime) as convertedtodate

Result: 2011-05-31 00:00:00.000

How do I do it in SQLite?

Second, what is the equivalent of MS SQL's datediff function on sqlite? E.g.:

datediff(Day,'5/30/2011 12:00:00 AM','5/31/2011 12:00:00 AM') as DateAge  

Result: 1

Answer 1

To answer your first question (convert string to date), the answer is "it depends". Since SQLite does not have a specific date field, you may not need to convert it. You could just store it in a string field (the options are sting, real, or int for date storage). If you want to convert the string to an int (which would be the number of seconds since 1970-01-01), you would use the strftime method like so:

strftime('%s','2011-05-12 01:03:00')

As for the second part of your question (difference between two dates), you would use the following code in SQLite:

strftime('%s','2011-05-12 01:03:00') - strftime('%s','2011-05-08 11:54:09')

That will give you the number of seconds between the two dates. You can play around with this information, as well as a lot more, to get exactly what you are looking for from SQLite. Here are a couple resources that will help you out:

http://www.sqlite.org/cvstrac/wiki?p=DateAndTimeFunctions

http://www.sqlite.org/datatype3.html