如何在SQLite中的日期之间使用 [英] How to use between for dates in SQLite

问题描述

我在Visual Studio .net中使用Sqlite.我有一个表，其中当我在查询之间的日期不提供结果时，列数据类型为日期时间.查询如下:

1)在``9/11/2012''和``9/13/2012''之间从boo_date中选择boo_date中的itm_id，book_acc


2)从boo_dmg_lost WHERE(boo_date>``9/23/2012'')AND(boo_date<``9/13/2012'')
中选择itm_id，book_acc

在此先感谢.

Hi,

I am using Sqlite with visual studio .net. I have a table in which a column data type is in datetime when I am using a query for between date it don''t giving results. Query listed below:

1) SELECT itm_id, book_acc FROM boo_dmg_lost WHERE boo_date between''9/11/2012'' and ''9/13/2012''


2)SELECT itm_id, book_acc FROM boo_dmg_lost WHERE (boo_date > ''9/23/2012'') AND (boo_date < ''9/13/2012'')


Thanks in advance.

推荐答案

由于列为DateTime类型，请尝试将其与日期而不是字符串进行比较:

Since the column is of DateTime type, try to compare it with dates and not string:

--DECLARE @startDate DateTime
--DECLARE @endDate DateTime

--SET @startDate = CONVERT(DateTime, '4/2/2011')

SELECT
  item_id, book_ac
FROM
  boo_dmg_lost
WHERE
  boo_date BETWEEN @startDate AND @endDate

如果需要，请阅读有关数据类型转换的信息: MSDN:CAST和CONVERT(Transact-SQL) [ ^ ]

If needed, read about datatype conversion: MSDN: CAST and CONVERT (Transact-SQL)[^]

我得到了正确的结果.

第一个插入查询的格式为 yyyy-mm-dd hh:mm:ss.xxxxxx
例如"2012-09-14 10:00:00.123123"，在此之后，我们必须阅读
这样的查询:
从boo_dmg_lost中选择itm_id，book_acc在strftime(''％Y-％m-％d''，boo_date)之间在"2012-09-14"和"2012-09-15"之间
由于用于将日期插入数据库的.Net的简单日期格式不是可读格式，因此我们将必须通过给定格式进行插入查询，然后再读取.

谢谢.

I got the correct result.

First insert query is in the Format of yyyy-mm-dd hh:mm:ss.xxxxxx
e.g ''2012-09-14 10:00:00.123123'' and after that for reading we will have to give
query like this:
SELECT itm_id, book_acc FROM boo_dmg_lost WHERE strftime(''%Y-%m-%d'', boo_date) BETWEEN ''2012-09-14'' AND ''2012-09-15''
Because simple date format of .Net for inserting date into database is not readable format so we will have to give insert query through given format and then read.

Thank You.

在下面给出，

1)从boo_dmg_lost中选择itm_id，book_acc在``20120911''和``20120913''之间的boo_date

2)2)从boo_dmg_lost WHERE(boo_date>``20120923'')AND(boo_date<``20120913'')中选择itm_id，book_acc

现在应该可以了.
谢谢

hi ,

give to there below that,

1) SELECT itm_id, book_acc FROM boo_dmg_lost WHERE boo_date between''20120911'' and ''20120913''

2)2)SELECT itm_id, book_acc FROM boo_dmg_lost WHERE (boo_date > ''20120923'') AND (boo_date < ''20120913'')


now it should works.
Thanks

这篇关于如何在SQLite中的日期之间使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！