sql  server (datetime) assist please

peterdarazs
peterdarazs used Ask the Experts™
on
Hi Experts -
We're selecting clients by visit date  but can't seem to get any selective results.


cn.ConnectionString = "Provider=SQLNCLI.1;Integrated Security=SSPI;" & _
        "Persist Security Info=False;" & _
        "AttachDBFileName=c:\CLIENT_RECORDS.mdf;Data Source = ASUSPeter\SQLEXPRESS"

rs.CursorLocation = adUseClient
rs.Open "Select * from VISITS where [visitdate] > " & DateValue(DateAdd("m", -60, Now)), cn

The data is in [9/09/2005 12:00:00 AM] format and ranges from (2001 - present).  As the query stands here, it returns all the records in the table.
(Alternatively, no records when trying  [<] )

Can anyone explain or fix  this?

Thanks

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jagdish DevakuSr DB Architect
Commented:
hey... i think the following site helps you...

http://msdn.microsoft.com/en-us/library/aa237895(SQL.80).aspx

try this...


Select * from VISITS where CONVERT (nvarchar, [visitdate], 20) > '2008-05-01' (not tested)

let me know if this is not working...

bye

what is DateValue function is doing?
try using below, it may resolve your problem

rs.Open "Select * from VISITS where convert(varchar, [visitdate],120) >DateAdd(mm,-60,getdate())", cn

Author

Commented:
Thanks RiteshShah - that line works perfectly, and thanks also Jagdish for your suggestion - it must've been quite close but it threw up an error message ? maybe just something to do with the date format - get date worked perfectly though??? thanks anyway. Peter
>>Author Comments:
Thanks RiteshShah - that line works perfectly, and thanks also Jagdish for your suggestion - it must've been quite close but it threw up an error message ? maybe just something to do with the date format - get date worked perfectly though??? thanks anyway. Peter<<

Hi Peter,

Glad to help!!!!

http://www.SQLHub.com

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial