Dates with time stamps

[DBAMJA]

I just ran accross a problem in one of my databases that I wasn't aware of until just now. It is probably just because of my own short sightedness but I thought I would pass along the information for those of you who are unaware of the same sort of situation.

I generate several reports out of a database that is the front-end to an SQL server backend. The date fields on the SQL server have time stamps as part of the date/time field (12/1/2003 12:00:21 PM). Alot of the reporting that is done is using date ranges and I use BETWEEN two different dates to get my results. What I wasn't aware of was that when using BETWEEN and there is a time stamp on the date, anything with the 'to date' that has a time stamp will not be included.

Example:
Between #7/1/2002# and #11/24/2003# will only result in data between 7/1/2002 and 11/23/2003 since all of the dates have the time stamp.

I was not aware of this 'little' problem in my program and now have to go back and rework the code to reformat the date field but I thought that I would pass along my error so others would not make the same mistake.

[flush]

DBAMJA

It is said that God will give you no more than you can handle. I just wish God didn't have so much faith in me.

 

[fredk]

dbamja -

Thanks for the heads up on that - just curious on what you intend to do to fix the problem? would formatting the date in the query work?

Thanks!

Fred

 

[cmmrfrds]

As Fred suggested, the Format is an easy way to strip out the time portion of the date.

Format(yourdate,"Short Date&quot Between #7/1/2002# and #11/24/2003#

 

[fredk]

thanks cmmrfrds -

Fred

 

[DBAMJA]

That's exactly what I am doing but just didn't get a chance to reply back to this earlier. Thanks.

[flush]

DBAMJA

It is said that God will give you no more than you can handle. I just wish God didn't have so much faith in me.

 

[Golom]

A somewhat nastier(?) way of handling it is

   myDate BETWEEN [Enter StartDate:] AND
          DateAdd ( "s", -1, DateAdd ("d", 1, [Enter End Date:]))

so that an "End Date" like 12/15/03 becomes 12/15/03 23:59:59