How to sql query between to dates like...

[jonny]

I ran this query in sql server and it works good.

SELECT Availability FROM ProductivityData WHERE oDate BETWEEN
'12/18/2008' AND '12/22/2008'


When I took it to VB.net I get an error message. Does anyone know how
I can get this query to work in VB.net?

 

[jonny]

I ran this query in sql server and it works good.

SELECT Availability FROM ProductivityData WHERE oDate BETWEEN
'12/18/2008' AND '12/22/2008'

When I took it to VB.net I get an error message. Does anyone know how
I can get this query to work in VB.net?

Never mind I just figured it out.

WHERE oDate >= ('12/18/2008') AND oDate <= ('12/22/2008')

http://www.programmersheaven.com/mb/database/78538/78538/sql-between-dates/

Thanks anyway!

 

[Alex Clark]

Well that raises a few questions...

See inline:

I ran this query in sql server and it works good.

Can you define "in sql server". What version of SQL Server, and how/where
did you execute the query? Was it via SQL Management Studio or some other
tool?

SELECT Availability FROM ProductivityData WHERE oDate BETWEEN
'12/18/2008' AND '12/22/2008'

When I took it to VB.net I get an error message. Does anyone know how
I can get this query to work in VB.net?

What error message? How did you "take it to VB.NET"? Are you executing the
query via ADO.NET inside your app?

Never mind I just figured it out.
WHERE oDate >= ('12/18/2008') AND oDate <= ('12/22/2008')

There's no reason the query should work any differently when executed from
ADO.NET as opposed to inside any of the other SQL tools. The SQL engine is
what's executing that query, not your application or the query tool. One
thing I would recommend is using a more robust way of passing date values in
to the query - if you run your application on machines with other default
locales (such as dd/MM/yyyy) then the query will fail under some
circumstances (if you're lucky) or return confusing results (if you're not).

-Alex

 

[Patrice]

Never post about an error without telling what it is. The error message is
the first diagnosis tools.

I would recommend using 'YYYYMMDD' that is always understood. The
significance of 12/18/2008 depends on server settings. Better yet use
parameters to pass the date (@Value).

For now I would say you have something wrong in the VB code rather than in
your query...

 

[Rod Gill]

If there is any chance of your application having to work internationally, I
always favor:
BETWEEN '2008-12-28' AND '2008-12-28' as that yyyy-mm-dd works anywhere in
the world.
--

Rod Gill
Microsoft MVP for Project

Author of the only book on Project VBA, see:
http://www.projectvbabook.com

Well that raises a few questions...

See inline:



Can you define "in sql server". What version of SQL Server, and how/where
did you execute the query? Was it via SQL Management Studio or some other
tool?


What error message? How did you "take it to VB.NET"? Are you executing
the query via ADO.NET inside your app?


There's no reason the query should work any differently when executed from
ADO.NET as opposed to inside any of the other SQL tools. The SQL engine
is what's executing that query, not your application or the query tool.
One thing I would recommend is using a more robust way of passing date
values in to the query - if you run your application on machines with
other default locales (such as dd/MM/yyyy) then the query will fail under
some circumstances (if you're lucky) or return confusing results (if
you're not).

-Alex




__________ Information from ESET Smart Security, version of virus
signature database 4478 (20091003) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4478 (20091003) __________

The message was checked by ESET Smart Security.

http://www.eset.com