[thelist] Issues working with dates

[Todd Richards]

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 !