查询搜索数据“从日期到日期" [英] Query for Searching data 'from Date to Date'

问题描述

我将ms-access用作数据库后端，并将Visual Studio 08用作Windows应用程序中的前端.

对于现金表，我有Date列，其数据类型为字符串，以便可以以自定义格式存储数据，例如"12 -Jan -2011".

此值来自dateTimePicker.Text属性.
我使用了dateTimePicker的自定义属性.

对于插入，更新和删除来说，它工作正常，但是当我运行查询以查找数据之间的日期"时，它没有给出正确的结果.

用最少的修改就能解决此问题的最佳选择是什么?
:confused :: confused:

问候，
*已删除邮件*

I am using ms-access as database backend, and visual studio 08 for frontend in windows application.

For a cashbook table, I have Date column whose data type is string so that I can store data in my custom format like ''12 -Jan -2011'' .

This value is coming from dateTimePicker.Text property.
I have used custom property of dateTimePicker.

It is working fine for insert, update and delete, but when I am running query to find data ''date between'', it is not giving proper result.

what can be best option to resolve this problem with least modification ?
:confused::confused:

Regards,
*REMOVED MAIL*

推荐答案

日期列的类型为字符串(varchar，nvarchar等).因此，当您使用between子句选择此列时，SQL无法完全选择所需的结果.

请注意，between子句仅适用于某些数据类型，例如DateTime，SmallDateTime，int，...(仅日期时间和Number).

请在查询之前将日期列转换为日期时间

Date column has type is string (varchar, nvarchar, ...). So, when you select this column with between clause, SQL cannot select exactly the result which you want.

Notice that, between clause only apply to some datatype, such as DateTime, SmallDateTime, int,... (datetime and Number only).

Please cast Date column into datetime before querying

SELECT Date FROM CashBook WHERE cast(Date as datetime) BETWEEN ''...a from date here'' AND ''...a to date here''

Andy

Andy

您的问题特别是您正在使用字符串.
它将在字符串的值之间搜索，而不是在日期之间搜索.

您将需要将Date作为字符串转换为Date作为Date.

或者，更容易地，使用日期"选项之一来正确设置日期格式，并以年月年"的格式指定日期格式.

这将完全消除该问题.

Your problem is specifically that you are using a string.
It will search between the values of the strings, not between dates.

You will need to convert the Date as a string to a Date as a Date.

Or, much easier, use one of the Date options to format the date properly, giving it''s format in a Day Month Year style.

That would obviate the problem altogether.

这篇关于查询搜索数据“从日期到日期"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！