SQl Query for Dates

[lokeshsu]

Hi all,

I have created the query for getting a data between dates in access, but when i see the result it takes the start date but excludes the end date. for example if i want the data from 1st Jan 11 till 31st Jan 11 i will get the data from 1st Jan 11 till 30th Jan 11. so i need a query which should include the last date and the first day data.


thanks in advance
Lokesh Sundar

 

[james_lankford]

where your_date_field between #01/01/2011# and #01/31/2011#

 

[lokeshsu]

i tried that in access but this excludes #01/31/2011# and gives the output only till #01/30/2011#.

 

[james_lankford]

then you don't have any data for 1/31

between is inclusive, it includes data for the start date, the end date and all dates inbetween

 

[Joe4]

Also be cognizant that date fields in Access also have time components. Change the format on your Access fields to show the time piece along with the data. If the time piece isn't all zeroes, you may need to modify your criteria to account for this.

#01/31/2011# assumes midnight and is equivalent to #01/31/2011 12:00:00 AM#

If you have a datetime value like this, #01/31/2011 1:00:00 AM#, it will fall outside of your paremters. So you might need to use something like:
#01/31/2011 11:59:59 PM# as you upper bound instead.

 

[lokeshsu]

Thanks it is working

 

[james_lankford]

Also be cognizant that date fields in Access ...

ooh, fantastic catch !
I forgot all about that