SQL statement to search records based on dates

Posted on 2006-05-03
Last Modified: 2010-08-05
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?  
Question by:limhodav
    LVL 142

    Assisted Solution

    by:Guy Hengel [angelIII / a3]
    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)) "


    Author Comment

    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.
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    what database do you have ? as I posted, I assumed Microsoft SQL Server (which is not the same as Microsoft Access)
    LVL 2

    Accepted Solution

    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...

    Author Comment

    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

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
    Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now