Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2010
    Posts
    26

    Unanswered: return all records that dates fall between two dates using sql:

    Hi all
    I'm using VS2010 and MySQL on win7 X64
    I' currently trying query orders that fall in between two dates in Visual Basic..
    The from date is to be 1 year from the current date and of course the the to date is the curent date
    This is the Code I'm using.

    Code:
    Dim FromDate As Date = DateAdd("yyyy", -1, Now)
                  FromDate = Format$(FromDate, "yyyy,MMM,dd")
            Dim todate As Date = Format$(Now, "yyyy,MMM,dd")
    "SELECT * FROM project WHERE PrjDate BETWEEN " & FromDate & " AND " & todate
    Thanks in Advance
    VIPER

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Using the between constuct requires the lower value first.
    To set the upper limit,the 1 year from now use the date time functions in either VB OR MySQL.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Mar 2010
    Posts
    26
    Thanks for your reply
    Bu I do Have the lowest value first
    Code:
    Dim FromDate As Date = DateAdd("yyyy", -1, Now)
    this line subtracts one year from the current year.
    and the query does not return any records.
    VIPER

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If you want to use a date in an SQL command it should follow this format
    Code:
    yyyy-mm-dd hh:mi:ss.nnn
    George
    Home | Blog

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    can we see the actual SQL you are sending to the server, NOT the VB trhat creates it, but the actual SQL

    can we see the code (the VB) that

    at present Im suspicious of
    Code:
    Dim FromDate As Date = DateAdd("yyyy", -1, Now)
    FromDate = Format$(FromDate, "yyyy,MMM,dd")
    you are assigning the value of a variable back to the same variable. a datetime variable has no intrinsic idea of a format (its a variable that stores date time information.

    the MySQL ODBC interface may be smart enough to realise that its dealing with datetime variable and treat it accordingly. however Im pretty certain that MySQL uses a different method for handling dates

    Code:
    Dim FromDate as string
    DimTodate as String
    dim strSQL as string
    FromDate = format(datediff("yyyy", 1, now()), "yyyy-mm-dd hh:mi:ss")
    Todate = format(now(),"yyyy-mm-dd hh:mi:ss")
    strSQL = "SELECT * FROM project
     WHERE PrjDate BETWEEN " & fromdate & " AND " & todate & ";"
    or in MySQL syntax
    Code:
    strSQL = "SELECT * FROM project
     WHERE PrjDate BETWEEN  addate(now(), interval -1 year) AND NOW();"
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Mar 2010
    Posts
    26
    This Is the SQL that is being sent to the server.
    Code:
    "SELECT * FROM project WHERE PrjDate BETWEEN 4/5/2012 AND 4/5/2013"
    VIPER

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    ...and have you executed that directly against the database?

    Can you change it to
    Code:
    SELECT *
    FROM   project
    WHERE  PrjDate BETWEEN 2012-05-04 AND 2013-05-04
    See why date formats are important? Because I'm in the UK I assumed that the 5 in your date was the month, whereas our American brethrin would think that the month was 4.
    George
    Home | Blog

  8. #8
    Join Date
    Mar 2010
    Posts
    26
    Thanks gvee for you reply
    the formats are as follows YYYY/MM/DD
    I Have confirmed that prjDate is a Date field.
    I have tried your query on the Database and returned no records.
    I Currently have 3 records with the following dates 2012-12-02, 2012-10-22,
    2013-01-03
    I also tried the following and no records were turned

    Code:
    SELECT *
    FROM   project
    WHERE  PrjDate BETWEEN 2012-01-01 AND 2013-01-01
    VIPER

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My I suggest using MySQL dates instead of numeric expressions, by using syntax like:
    Code:
    SELECT *
       FROM   project
       WHERE  PrjDate BETWEEN '2012-01-01' AND '2013-01-01'
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Mar 2010
    Posts
    26
    Thank you all for your help
    I tried your suggestion and it worked.
    so I modified my code and now I get this query that works.
    Code:
    "SELECT * FROM project WHERE PrjDate BETWEEN '2012-04-06' AND '2013-04-05'"
    VIPER

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    did you try
    Code:
    "SELECT * FROM project 
     WHERE PrjDate BETWEEN  ADDDATE(now(), INTERVAL -1 YEAR) AND now();"
    the advantage of the above approach is that it always retrieves the last years values as opposed to having to supply a start and end date.

    the version supplied by PatP will work providing that you haven't stored the time element.
    what that will return is any rows that are between
    06 April 2012 @ 00:00:00 and 05 April 2013 @ 00:00:00

    if you haven't stored a time then its not a problem, but otherwise it will will return all rows for 06 April 2012 upto and including 04 April 2013 And rows that have a time stamp of 05 April 2013 @ 00:00:00. if you have time elements stored then effedct
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •