[thelist] Issues working with dates

[Ken Schaefer]

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 !