Using Date in SQL Statement

[Chlaris]

Dear all,

I'm using Ms SQL as back end and create linked table with ODBC connection.
I write SQL statement like below:

SELECT * FROM tblInvoiceMain WHERE InvoiceDate Between #" &
Format(dteBeginDate, "mm/dd/yyyy") & "# And #" & Format(dteEndDate,
"mm/dd/yyyy") & "#"

After I execute, the query returns wrong result.
How should I write the SQL statement for date parameter ?
Thanks.

Chlaris

 

[Dirk Goldgar]

Dear all,

I'm using Ms SQL as back end and create linked table with ODBC connection.
I write SQL statement like below:

SELECT * FROM tblInvoiceMain WHERE InvoiceDate Between #" &
Format(dteBeginDate, "mm/dd/yyyy") & "# And #" & Format(dteEndDate,
"mm/dd/yyyy") & "#"

After I execute, the query returns wrong result.
How should I write the SQL statement for date parameter ?

In what way is the result wrong?

 

[Chlaris]

The query returns only 4 records.
Actually there are hundred of records.

 

[Armen]

User Where clouse without #
WHERE InvoiceDate Between '01/01/2000' And '01/01/2008

 

[Chlaris]

Sorry, I misread the data.
I think my SQL statement is already true.

 

[a a r o n . k e m p f]

User Where clouse without #
WHERE InvoiceDate Between '01/01/2000' And '01/01/2008

 

[John W. Vinson]

User Where clouse without #
WHERE InvoiceDate Between '01/01/2000' And '01/01/2008

That's correct if your data is in SQL/Server and you're using a passthrough
query or have the query set to use ADO syntax. If it's an Access JET table the
# delimiters are correct.

 

[a a r o n . k e m p f]

in other words-- JET doesn't follow industry standards

 

[posted_by_anonymous]

in other words-- JET doesn't follow industry standards

In other words, aaron doesn't follow newsgroup netiquette standards, and
sometimes does not even follow the law.

He's just a rattlebrain numbskull... every database engine has its own
"dialect".

Not every database engine has the option to specify to limit a particular
database to standard SQL, but Jet does. aaron, do you have a selectable
option to limit you to standard newsgroup netiquette?

Anony Mous

 

[George Hepworth]

IMO, the best way to deal with trolls is to ignore them personally, with one
exception.

Sometimes it is necessary to post a correction to an inaccurate or
misleading statement by a troll.

However, the average person who posts here is smart enough to figure out who
the trolls are all by themselves.


George