SQL Query problem

[PORRASTAR]

Hi
I have a date field in my sql 2000 DB called "dttripstart" It the following value:"2000-11-22 06:39:49.000" I am trying to do a select statement where dttripstart = '2000-11-22 ' How do I specify this?
Thanking you in advance
PORRASTAR

[CADMANdo]

Because u have allowed times in the field then u will have to use one of the following methods

These are part of the where clause :-

dttripstart >= #22/Nov/2000# And dttripstart <#23/Nov/2000#

or

cdate(format(dttripstart,'dd/mmm/yyyy')) = #22/Nov/2000#

[PORRASTAR]

If i use the following query: select * from tripdata where dttripstart >= #22/Nov/2000# And dttripstart <#23/Nov/2000#
I get the following error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '#'.

If I use the following query:select * from tripdata where cdate(format(dttripstart,'dd/mmm/yyyy')) = #22/Nov/2000#
Then I get the following error:Server: Msg 195, Level 15, State 10, Line 1
'format' is not a recognized function name.
Any idea as to what is wrong?

[swatty]

First error is because you have to put a space after the < sign in <#23/Nov/2000#


the second one because the format is to be done on vb's side

"select * from tripdata where " & cdate(format(dttripstart,"dd/mmm/yyyy")) & " = #22/Nov/2000#"

[CADMANdo]

Try following:-

SELECT tripdata.*
FROM tripdata
WHERE (((tripdata.dttripstart)>=#11/22/2000# And (tripdata.dttripstart)<#11/23/2000#));


MS2000 may not allow format