查找两个日期之间的生日的问题 [英] Problem finding Birthdates between two dates

问题描述

我有一个名为"Person"的表，该表的列为"DOB"，其中包含所有出生日期.当我尝试运行指定范围开始日期的查询时，我得到预期的输出，但是指定范围结束日期时，我没有任何行.

这是指定范围开始日期的查询:

Select Format(DOB,"d, mmmm") as [BirthDate] from Person where Format((Format(DOB,"m/d")+"/2011"),"Short Date")>=''7/1/2011''

这是同时指定范围开始和结束日期的查询:

Select Format(DOB,"d, mmmm") as [BirthDate] from Student where Format((Format(DOB,"m/d")+"/2011"),"Short Date")>=''7/1/2011''  and Format((Format(DOB,"m/d")+"/2011"),"Short Date")<=''7/15/2012''

表格中有包含DOB的行，其中DOB为7/3/2001、7/4/2001和其他.

我想要一个查询，通过该查询我可以指定开始日期和结束日期，并在该范围内搜索生日，而这些日期实际上是由应用程序的用户在运行时由datetimepicker控件设置的.无法理解我在哪里犯了错误.

解决方案

不要(永远)使用字符串作为日期.所有语言和数据库都有完全有用且可用的DateTime结构和类，可以处理此类问题.您唯一需要将DateTime转换为字符串的时间是何时要在某个输出设备上显示它.

实际上，稍后我在C#的datagridview中需要此输出.应用.那么，是否有任何这样的预定义函数可以在MS-Access中获得这样的输出，或者我可以在ADO.Net命令字符串中使用哪个查询，从而可以通过datetime选择器控件指定的任何动态开始和结束日期来获得这样的输出? >

您应该能够通过使用DATE()来消除时间戳记部分的影响，然后仅基于日期选择器中格式化的日期字符串进行比较，因此您的sql最终类似于

... WHERE DATE(DateField)> ``2011年7月1日''且DATE(DateField)< ''2011年7月16日''

要获取该格式...

DateTime.ToString("MM-dd-yyyy");

I have a table named "Person" having a column as "DOB" holding all Birthdates. When I try to run the query specifying range starting date I get expected output but on specifying range ending date I get no rows.

Here is the query having range starting date specified:

Select Format(DOB,"d, mmmm") as [BirthDate] from Person where Format((Format(DOB,"m/d")+"/2011"),"Short Date")>=''7/1/2011''

Here is the query having both range start and end date specified:

Select Format(DOB,"d, mmmm") as [BirthDate] from Student where Format((Format(DOB,"m/d")+"/2011"),"Short Date")>=''7/1/2011''  and Format((Format(DOB,"m/d")+"/2011"),"Short Date")<=''7/15/2012''

in the table there are rows having DOB with 7/3/2001,7/4/2001 and others.

I wanted a query whereby I can specify the starting and ending dates and search birthdates between that range and the dates are actually set by datetimepicker controls at runtime by user of the application. Can''t understand where is the mistake I have made.

解决方案

Don''t (ever) use strings for dates. There are perfectly useful and usable DateTime structures and classes in all languages and databases that can handle issues like this. The only time (ever) that you need to convert a DateTime to a string is when you want to display it on some output device.

Actually I need this output later in datagridview in my C# application. So is there any such predefined function to get such output in MS-Access or which query I may use in ADO.Net command string whereby such output can be obtained with any dynamic starting and and ending date specified by datetime picker controls?

You should be able to nullify the effect of the time stamp portion by using DATE(), then you just compare based on formatted date strings from your datepickers, so your sql ends up with something like

...WHERE DATE(DateField) > ''07-01-2011'' AND DATE(DateField) < ''07-16-2011''

To get that format...

DateTime.ToString("MM-dd-yyyy");

这篇关于查找两个日期之间的生日的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！