Problem finding Birthdates between two dates

Question

0.00/5 (No votes)

See more:

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.

Posted 15-Jul-11 4:30am

Vikram_

Updated 15-Jul-11 6:38am

v2

Add a Solution

Comments

GenJerDan 15-Jul-11 14:33pm

It probably isn't an issue in this case, but for the future: beware of your ending date. If there is any possibility that the time portion of the DateTime won't be null, that <= 7/15/2012 won't get you the ones born on the 15th in most cases.

Safer to add a day and just do a less-than, i.e. < 7/16/2012

3 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard MacCutchan · Answer 1 · 2011-07-15T05:10:00

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.

Vikram_ · Answer 2 · 2011-07-15T06:03:00

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?

fcronin · Answer 3 · 2011-07-15T10:06:00

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");

Problem finding Birthdates between two dates

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Problem finding Birthdates between two dates

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us