Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2013
    Posts
    1

    Unanswered: Selecting data based on date field minus 1 year

    I have some date fields, within MySQL tables formatted as YYYY-MM-DD. My goal is to change a report from displaying Jan - December of the same year to displaying October of one year thru September of the following year. A code examples is:
    Code:
    Dim cQuery As String _
                    = "SELECT id, CONCAT(DATE_FORMAT(start, '%m/%d'), ' to ', DATE_FORMAT(end, '%m/%d')) AS duration, ROUND(DATEDIFF(end, start) / 7) AS weeks " & _
                    "FROM cycle " & _
                    "WHERE YEAR(start) = ?yid ORDER BY id"
    How can I start with the Year needed (October - December) and then add one to the ?yid field to return the data for January - September of the following year? The following is using the yid field.
    Code:
    For i As Integer = 10 To 12
                Dim searchQuery As String _
                    = Request.QueryString("yid") & "-" & String.Format("{0:d2}", i) & "-" & "01"
                Dim dr As DataRow = monthlyTotals.Rows.Find(searchQuery)
                ...DO SOME THINGS........ 
    For i As Integer = 1 To 9
                Dim searchQuery As String _
                    = Request.QueryString("yid") & "-" & String.Format("{0:d2}", i) & "-" & "01"
                Dim dr As DataRow = monthlyTotals.Rows.Find(searchQuery)
                ....DO MORE THINGS......
    Any assistance would be greatly appreciated. I am not what one would call a "solid" SQL programmer. I feel a bit over my head in this one. It seemed like such a simple change from displaying Jan - December to October - September.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I haven't used VB/VBA for a while, but my gut reaction is that I'd change the yid argument to be a date instead of an integer then use:
    Code:
    Dim cQuery As String _
    = "SELECT id, CONCAT(DATE_FORMAT(start, '%m/%d')" _
    & ",  ' to ', DATE_FORMAT(end, '%m/%d')) AS duration" _
    & ",  ROUND(DATEDIFF(end, start) / 7) AS weeks " & _
    & "   FROM cycle " _
    & "   WHERE start BETWEEN ?yid - INTERVAL 1 year AND ?yid" _
    & "   ORDER BY id"
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

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