在数据被存储为字符串时，对日期和时间的SQL语句。

HI, I have stored data & time as varchar type and data looks like this "9/16/2010 2:59:10 PM". Now I want to retrieve data from between two dates such as From 01/01/2010 to 31/10/2010, When I run this SQL Statement:

您好，我已经将数据和时间存储为varchar类型，数据看起来是这样的:“9/16/2010 2:59:10 PM”。现在，我想从两个日期之间检索数据，比如从2010年1月1日到2010年10月31日，当我运行这个SQL语句时:

SELECT     username, timein, timeout
FROM         user_log
WHERE     (timein BETWEEN '01/01/2010' AND '30/11/2010')

It returns me nothing. So How should I resolve this. Thanks.

它返回我什么。我该如何解决这个问题。谢谢。

4 个解决方案

#1

HI, I have stored data & time as varchar type and data looks like this "9/16/2010 2:59:10 PM"

你好，我已经将数据和时间存储为varchar类型，数据如下“9/16/2010 2:59:10 PM”

Don't do that. If your database has date/time types, use them! And if it doesn't (e.g., SQLite), then store your dates in ISO 8601 order (2010-09-16 14:59:10) so they'll sort correctly.

不要这样做。如果您的数据库具有日期/时间类型，请使用它们!如果它不(例如，SQLite)，那么将你的日期存储在ISO 8601订单(2010-09-16 14:59:10)，这样他们就能正确排序。

#2

You shouldn't expect date-based comparisons to work if the data isn't a real date, in terms of database data types. Your options are:

就数据库数据类型而言，如果数据不是真实的数据，则不应该期望基于日期的比较有效。你的选择是:

1) Upgrade the column into a date/time based data type.

1)将列升级为基于日期/时间的数据类型。

2) As another user wrote, cast each string into a date. This option, unlike the first, will perform poorly.

2)正如另一个用户所写的，将每个字符串转换为一个日期。与第一个不同的是，这个选项的性能会很差。

#3

You have to CAST the strings to DATE or DATETIME

您必须将字符串转换为DATE或DATETIME

SELECT     username, timein, timeout
FROM         user_log
WHERE     (CAST(timein AS DATE) BETWEEN CAST('01/01/2010' AS DATE) AND CAST('30/11/2010' AS DATE))

EDIT - Added a cast around the timein field

编辑——在timein字段中添加一个cast

#4

If it's stored as a string, then treat it as a string. You can't compare the two strings 9/16/2010 and 30/11/2010 and expect anything useful (i.e. first digit 3 comes before 9). The first choice would be BETWEEN '1/1/2010' AND '11/30/2010'. If all your data is from one year it may work, though I'm not sure if the varying number of digits (not 0 padded) will work. With more than one year, 1/1/2010 is less than 2/1/2009 (first digit 1 comes before 2). If you had fixed width and started with the year (e.g. 2010-01-02), then it should work fine.

如果它被存储为字符串，那么将它视为字符串。你无法比较9/16/2010和30/11/2010这两个字符串，并期望任何有用的东西(例如，第一个数字3在9之前)。如果你所有的数据都来自于一年，那么它可能是有效的，尽管我不确定不同的数字(不是0填充)是否有效。如果你有固定的宽度，并且从年初开始(例如，2010-01-02)，那么它就可以正常工作了。

With the data you have, the best option is to cast each string to a date, then comparisons are easy. If you can somehow convert the field to datetime it would be best. In MS SQL Server you could create an indexed computed field which would convert the string to a date and index for fast querying.

有了这些数据，最好的选择是将每个字符串转换为一个日期，然后比较就很容易了。如果你能以某种方式将字段转换为datetime，那就最好了。在MS SQL Server中，您可以创建一个索引计算字段，该字段将字符串转换为日期和索引，以便进行快速查询。

#1