[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 199
  • Last Modified:

SQL statement to search records based on dates

Dear experts,

I have a simple qn that has dumbfounded me and have not been able to find any solutions.

I create a Date field in my SQL database and have records in it. I use ADODB connection to connect to my VB program.  

But this simple SQL statement refuses to work             rs.Open "Select * from Testing Where Date = 03/05/2006"

Alternatives that I have tried include:-

1)  putting single quotes around the date like '03/05/2006'  but to no success.  
2)  testing other fields to know the connection works cos I could get results from them.
3)  Reiterate thro the recordset  
      Do While Not rs.EOF
      MsgBox rs!Date
      i could actually see the date 03/05/2006 appearing

what have I done wrong?  
  • 2
  • 2
2 Solutions
Guy Hengel [angelIII / a3]Billing EngineerCommented:
assuming SQL Server:
   rs.Open "Select * from Testing Where Date = convert(datetime, '2006-05-03', 120) " 

better even, to avoid problems when time portion is stored in the field:

   rs.Open "Select * from Testing Where [Date] >= convert(datetime, '2006-05-03', 120)  AND [Date] < dateadd(day, 1, convert(datetime, '2006-05-03', 120)) " 

limhodavAuthor Commented:
I copied the code but it contains syntax error and I found out it wasn't evaluating.  

Using MsgBox Gives the exact string above.

I've also tried Cdate and Format function but didn't work too. I've spent 4 hours on this problem. Sigh ....  

Btw, My Sql database only contains a Date field, NOT datetime.
Guy Hengel [angelIII / a3]Billing EngineerCommented:
what database do you have ? as I posted, I assumed Microsoft SQL Server (which is not the same as Microsoft Access)
Try this:

rs.Open "Select * from Testing Where [Date] = "  & chr(39) & "05/03/2006"  & chr(39)

if you don't get any results it is probably because you have timestamp on your field, then you would have to test a date interval:
>= 05/03/06 and < 05/04/06 or use the dateadd function...
limhodavAuthor Commented:
Dear all,

I have solved this problem by using this statement instead. Yes, I'm using mySQL database.
Actually, wat I wanted to do is to retrieve records based on the selected month n year.

Instead of comparing dates, I have separated the month and year and compared it as below.

sql = "Select * from Pemakaian Where Month(Date) = " & Month1 & " And Year(Tanggal) =" & Year.Text & " Order by Id"

I didn't know u could use a function on an SQL field e.g. Month(Date) like that.  

As a form of appreciation for your help, I will split the points.

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now