SQLite 查询问题 [英] SQLite Query Questions

问题描述

2 个问题:

首先，在 MSSQL 中，我可以通过执行将 nvarchar 转换为日期时间

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

结果:2011-05-31 00:00:00.000

如何在 SQLite 中执行此操作?

其次，SQLite 上 MS SQL 的 datediff 函数的等价物是什么?例如:

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

结果:1​​

解决方案

要回答您的第一个问题(将字符串转换为日期)，答案是视情况而定".由于 SQLite 没有特定的日期字段，因此您可能不需要对其进行转换.您可以将其存储在字符串字段中(选项为 sting、real 或 int 用于日期存储).如果您想将字符串转换为 int(这将是自 1970-01-01 以来的秒数)，您可以像这样使用 strftime 方法:

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

至于问题的第二部分(两个日期之间的差异)，您将在 SQLite 中使用以下代码:

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

这将为您提供两个日期之间的秒数.您可以使用这些信息以及更多信息，以准确地从 SQLite 中获取您正在寻找的信息.这里有一些资源可以帮助您:

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

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

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

解决方案

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

这篇关于SQLite 查询问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！