DataReport/DataEnvironment questions...

[Spectre5000]

I have a question that I was wondering if anybody could answer.

I am trying to create a datareport object in VB6, using the dataenvironment object as the datasource.
I need a command in the data environment to be able to select records from a table in an SQL Server database
based on the current date. I looked at the MSDN library online, and got this line of code to select the
current date in the database:

SELECT GETDATE()

This line returns the date in the format mm/dd/yyyy hh:nn:ss AMPM. I need the format of this line to be
mm/dd/yyyy, dropping the time format altogether. Now, I also saw on the MSDN library web page how to cast and
declare variables, but when I try to do this I get an error at the first SELECT statement:

SET DATEFORMAT mdy
DECLARE @datevar datetime
SET @datevar = SELECT GETDATE()
SELECT * FROM REGISTER WHERE DATE = @datevar

The error is on the first select statement. When I take out the first select, and just make that line
SET @datevar = GETDATE(), the system returns the error "Operation is not allowed when the object is closed", but
when I have the SELECT statement in there, the system returns a generic "Incorrect syntax near the keyword SELECT."

Does anyone know how I can make this work? I really need to use this data report, and this report will be run daily,
hence the need for the ability of the data environment to get records based on the current date. Any help that anyone has
would be greatly appreciated! Thank you all in advance for at least looking at this!

Spectre5000

[jn8230]

I don't have much experience with SQL server, but I don't think you need to use SET.... you can convert the datetime field to mm/dd/yyyy using this:
CONVERT(varchar(10), GETDATE(),101)

The following lines should get you what you want..

DECLARE @DateVar datetime
SELECT @DateVar = CONVERT(varchar(10), GETDATE(),101)
SELECT * FROM REGISTER WHERE DATE = @DateVar

Hope that helps...

Jason