Comparing dates is a pain in the *%% as I'm sure you've found. Try
formatting the dates as ODBCDateTimeFormat() inside the SQL statement.
If you can't set the date with that function, the database isn't going to
read it correctly either.
---mark
=========================================
Mark Warrick - Fusioneers.com
Personal Email: [EMAIL PROTECTED]
Business Email: [EMAIL PROTECTED]
Phone: 714-547-5386
Efax: 801-730-7289
Personal URL: http://www.warrick.net
Business URL: http://www.fusioneers.com
ICQ: 125160 / AIM: markwarric
=========================================
-----Original Message-----
From: Skyward Internet Technology [mailto:[EMAIL PROTECTED]]
Sent: Friday, July 20, 2001 12:05 PM
To: CF-Talk
Subject: Re: (OT) Access SQL Statement
A dump of the PriSched table reveals that STime and ETime look like such:
1899-12-30 06:00:00 1899-12-30 07:00:00
1899-12-30 07:00:00 1899-12-30 11:00:00
So, what does the following query not work?
SELECT STime, ETime, Name, Describe FROM PriSched WHERE ((NOT STime
BETWEEN 1899-12-30 18:00:00 AND 1899-12-30 18:30:00) OR (ETime BETWEEN
1899-12-30 18:00:00 AND 1899-12-30 18:30:00)) AND ((NOT STime BETWEEN
1899-12-30 21:00:00 AND 1899-12-30 00:00:00) OR (ETime BETWEEN 1899-12-30
21:00:00 AND 1899-12-30 00:00:00)) ORDER BY STime, ETime"
It throws the following error. I've matched the datatypes in the SQL.
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in
query expression '((NOT STime BETWEEN 1899-12-30 18:00:00 AND 1899-12-30
18:30:00) OR (ETime BETWEEN 1899-12-30 18:00:00 AND 1899-12-30 18:30:00))
AND ((NOT STime BETWEEN 1899-12-30 21:00'.
Thanks,
Dave
----- Original Message -----
From: "Mark Warrick" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Friday, July 20, 2001 12:38 PM
Subject: RE: (OT) Access SQL Statement
>
>
> =========================================
> Mark Warrick - Fusioneers.com
> Personal Email: [EMAIL PROTECTED]
> Business Email: [EMAIL PROTECTED]
> Phone: 714-547-5386
> Efax: 801-730-7289
> Personal URL: http://www.warrick.net
> Business URL: http://www.fusioneers.com
> ICQ: 125160 / AIM: markwarric
> =========================================
> With Access and SQL, you put the NOT before an otherwise true condition.
>
> WHERE NOT StartTime BETWEEN 1899-12-30 18:00:00 AND 1899-12-30
> 18:30:00
>
> ---mark
>
>
> -----Original Message-----
> From: Skyward Internet Technology [mailto:[EMAIL PROTECTED]]
> Sent: Thursday, July 19, 2001 9:52 AM
> To: CF-Talk
> Subject: (OT) Access SQL Statement
>
>
> I'm used to Oracle, so I always choke on Access stuff . . .
> How do you do a NOT BETWEEN in Access?
> (Note: StartTime is a Date/Time Field)
>
> EX:
> SELECT StartTime
> FROM myTalbe
> WHERE StartTime NOT BETWEEN 1899-12-30 18:00:00 AND 1899-12-30
> 18:30:00
> ORDER BY StartTime
>
> Thanks,
> Dave
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
