Can anyone help

I am trying to perform a select query from within VB connecting to a SQL
Server 2000 database.

I am trying to select and in the where clause i want to search for a date.
The field 'MyField' in the database is set up as a datetime data type.

See code below.
---
dim rs as recordset
dim con
dim sqstring

con.Open = "Provider=SQLOLEDB.1;Data Source=(local);Initial
Catalog=MyDatabase;user id = 'sa';password='sa'"

sqlstring = "Select MyField from MyTable where MyField = '01/01/2004' "

rs.Open sqlstring, con, 1
----
I have also tried various other variations on how to represent the date
Nothing is coming back and there definately is data for the date. What is
the syntax for searcing for datetime data types through ADO?

Datetime datatypes hold just that Dates and Times. The Date time combo you
are asking for here is Midnight on January first 2004.

You could do

WHERE CONVERT(char(8),DateField,112) = '20040101'

Kalen Delaney did an excellent article in SQL Mag a while back about getting
date and time data from SQL Server.



--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Spud_Monkey" <spudmonkey (AT) aol (DOT) co> wrote

Is there data for 01/01/2004 00:00:00 ?

Try-

select * from table where date > '20040101' and date < '20040102'

Why is this posted to the DTS newsgroup?

--
Darren Green
http://www.sqldts.com

"Spud_Monkey" <spudmonkey (AT) aol (DOT) co> wrote