Thanks for the input Ken. I received some great feedback from someone last week, and your tips were the icing on the cake! Todd -----Original Message----- From: Ken Schaefer [mailto:Ken at adOpenStatic.com] Sent: Monday, May 11, 2009 1:39 AM To: todd at promisingsites.com; thelist at lists.evolt.org Subject: RE: [thelist] Issues working with dates Hi, a) There is no need to present yyyy/mm/dd to the end user. Just use string manipulation in your ASP page to format the date string (in VBScript) into yyyy/mm/dd formatted string, and put that into your ADO Command object (you are using ADO Command objects and not literal strings right?) b) Access itself has a Date() command - use that. It will generate an internal date representation that it can compare with it's own internally stored datetime data. Dates aren't actually stored in the way that you see them (as evidenced by the fact that you can change the way they are presented depending on your regional locale). They are usually stored as an integer indicating the number of days before or after some arbitrary starting point. strSQL = "SELECT EventID FROM Events WHERE Event_EndDate >= Date()" would give you all events that end today or later. On the other hand: strSQL = "SELECT EventID FROM Events WHERE Event_EndDate >= #" & Date() & "#" gives you something indeterminant, because in this case Date() is generated by ASP, turned into a string of some indeterminate format, and then concatenated to the previous string. Depending on the locale that ASP is running under you might end up with either: SELECT EventID FROM Events WHERE Event_EndDate >= #11/05/2009# or SELECT EventID FROM Events WHERE Event_EndDate >= #05/11/2009# Cheers Ken ________________________________________ From: thelist-bounces at lists.evolt.org [thelist-bounces at lists.evolt.org] On Behalf Of Todd Richards [todd at promisingsites.com] Sent: Saturday, 9 May 2009 8:06 AM To: 'Joel D Canfield'; thelist at lists.evolt.org Subject: Re: [thelist] Issues working with dates Hi Joel - Since calling date() yields mm/dd/yyyy, I assumed that I needed to format it so that it was the same as what was in the database (yyyy/mm/dd). Am I sure that I'm comparing dates, and not text strings? The columns in the database are datetime, but I am using a date picker to ensure consistency, and that is getting entered. So I would assume I'm comparing dates? What I've been trying is to set up my query so that Query = "select * from DB where event_end_Date <= " & eventDate (or date()) & " order by eventStartDate" Is that not correct? Todd -----Original Message----- From: Joel D Canfield [mailto:Joel at BizBa6.com] Sent: Friday, May 08, 2009 2:50 PM To: todd at promisingsites.com; thelist at lists.evolt.org Subject: RE: [thelist] Issues working with dates > Now I'm trying to not show the expired events. I'm calling the date(), > then > formatting it so that it's yyy/mm/dd format why not just work with as datetime data? > (which is set to the variable > "eventDate"). That part works. I'm trying to pull a query from the DB > "where event_End_Date >= eventDate", and I get all of the dates. If I do > event_End_Date <= eventDate", then I get no dates (as I shouldn't). I'm > testing it right now with one event with an end date of 7/9/2008, and > another with 3/12/2009. are you sure you're comparing dates, and not text strings? why not do this in SQL instead of the vbscript in the page? a possible hole in the logic: shouldn't you be comparing an event's date to the current date on the calendar? I mean, won't the event's end date *always* be greater than or equal to the event's date? most stuff ends after it started ;) joel -- * * Please support the community that supports you. * * http://evolt.org/help_support_evolt/ For unsubscribe and other options, including the Tip Harvester and archives of thelist go to: http://lists.evolt.org Workers of the Web, evolt !