none
SQL Sever Between Clause RRS feed

  • Question

  • Hi,

    How can we pass a group of dates(i.e. entire date column) in the between clause of SQL Query.

    Example:

    SELECT VAL FROM ANALOG WHERE TIME BETWEEN COL1_TIME AND COL2_TIME ORDER BY TIME.

    Here COL1_TIME and COL2_TIME are nothing but columns of some other table which i have passed into a multi-value parameter in SSRS,

    I am trying to pass an entire group of times in between clause.

    How can i retrieve the data using the above query?

    I also tried using filters which did not work.

    Could you please suggest the best possible solution to meet our requirement.

    Please help!


    Friday, October 26, 2018 6:44 AM

All replies

  • Hello,

    BETWEEN only works with inclusive values while a group is best done with a IN clause.

    So here we have two ranges, add more works the same. If you don't know the various ranges at any given time then you would need to have a base SELECT statement with no WHERE, append a WHERE to the base SELECT and add conditions into it.

    SELECT O.OrderID,
           C.CompanyName,
           O.EmployeeID,
           O.OrderDate,
           O.RequiredDate,
           O.ShippedDate,
           O.ShipVia,
           O.Freight,
           O.ShipAddress,
           O.ShipCity,
           O.ShipRegion,
           O.ShipPostalCode,
           O.ShipCountry
    FROM Orders AS O
         INNER JOIN Customers AS C ON O.CustomerIdentifier = C.CustomerIdentifier
    WHERE O.ShippedDate BETWEEN '2014-07-24' AND '2014-09-06'
          OR O.ShippedDate BETWEEN '2014-09-05' AND '2014-10-09';

    In code using a simple example will produce the same SQL as above.

    Public Class Form2
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            Dim dateRanges As New List(Of DateTimeItem) From
                {
                    New DateTimeItem With {.StartRange = New Date(2014, 7, 24), .EndRange = New Date(2014, 9, 6)},
                    New DateTimeItem With {.StartRange = New Date(2014, 9, 5), .EndRange = New Date(2014, 10, 9)}
                }
    
            Dim demo As New Example
            Dim fullStatement As String = $"{demo.CreateSelectStatement}{demo.CreateWhere(dateRanges)}"
    
            Console.WriteLine(fullStatement)
    
        End Sub
    End Class
    Public Class Example
        Public Function CreateWhere(ByVal pRange As List(Of DateTimeItem)) As String
            Dim sb As New Text.StringBuilder
            sb.Append("WHERE ")
            For Each item In pRange
                sb.Append($"O.ShippedDate BETWEEN '{item.StartRange.ToShortDateString}' AND '{item.EndRange.ToShortDateString}' OR ")
            Next
            Return sb.ToString.Substring(0, sb.ToString.LastIndexOf(" OR"))
        End Function
        Public Function CreateSelectStatement() As String
            Return <SQL>
    SELECT O.OrderID,
           C.CompanyName,
           O.EmployeeID,
           O.OrderDate,
           O.RequiredDate,
           O.ShippedDate,
           O.ShipVia,
           O.Freight,
           O.ShipAddress,
           O.ShipCity,
           O.ShipRegion,
           O.ShipPostalCode,
           O.ShipCountry
    FROM Orders AS O
         INNER JOIN Customers AS C ON O.CustomerIdentifier = C.CustomerIdentifier
    
                   </SQL>.Value
        End Function
    End Class
    Public Class DateTimeItem
        Public Property StartRange As Date
        Public Property EndRange As Date
    End Class


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites



    Friday, October 26, 2018 9:26 AM
    Moderator