Help make a SQL query ..

There is a table with dates:

id | date | startTime | endTime ---+------------+-----------+-------- 1 | 2018-06-02 | 00:00 | 02:00 2 | 2018-06-02 | 02:00 | 04:00 3 | 2018-06-02 | 18:00 | 19:00 4 | 2018-06-02 | 21:30 | 22:00

You need to get records from it that affect the incoming date.

For example, incoming 2018-06-02 15: 00-17: 00 will not return any, and 2018-06-02 18: 00-21: 40 will return 3 and 4 entries. Thank.

  • Do you want to send the date in exactly this format "2018-06-02 15: 00-17: 00"? - Novitskiy Denis
  • Yes, the incoming one comes this way, but it's not scary, you can first parse it into 3 parameters: date, startTime and endTime - mico
  • one
    Create a normal datetime from everything you use - teran
  • 2
    Why divide the date and time into 3 columns? You can also use DATETIME or timestamp! This Perversion - madfan41k
  • dateTime is ok, but the project is old and it’s done there, it’s too expensive to change and there may be bugs in other places - mico

2 answers 2

 DECLARE @InputData NVARCHAR(22) = '2018-06-02 18:00-21:40' DECLARE @InputDate DATE = SUBSTRING(@InputData, 1,10) DECLARE @InputTimeFrom TIME = SUBSTRING(@InputData, 12,5) DECLARE @InputTimeTo TIME = SUBSTRING(@InputData, 18,22) DECLARE @DataTable TABLE (id INT, date_column DATE, startTime TIME, endTime TIME) INSERT INTO @DataTable(id, date_column, startTime, endTime) VALUES (1 , '2018-06-02' , '00:00' , '02:00'), (2 , '2018-06-02' , '02:00' , '04:00'), (3 , '2018-06-02' , '18:00' , '19:00'), (4 , '2018-06-02' , '21:30' , '22:00') SELECT * FROM @DataTable WHERE date_column = @InputDate AND startTime BETWEEN @InputTimeFrom AND @InputTimeTo

If the fields of type varchar to this type need to bring variables

    Here is one of the solutions - it will work only if the beginning / end of the period for one date will be:

     select * from test where date = '2018-06-02' and startTime < '21:40' and endTime > '18:00';

    Here the first value is the end of the requested period, the second is the beginning.

    An example you can drive here.

    Yes, the solution is indicated from the assumption that the rows are displayed when the query interval and the interval in the line of the table intersect. That is, when prompted from 18:30 to 21:40, the same lines will also be displayed.