none
MSSQL Query between 2 Dates RRS feed

  • Question

  • Hello
    I'm working on programm for presence control
    e.g
    ID  -       CheckIn                       - CheckOut
    1     29/08/2019 10:00  --   29/08/2019 14:00
    2      29/08/2019 22:00  --   30/08/2019 06:00
    3     30/08/2019 10:00   --  30/08/2019 14:00
    If i do a query i can see all récords, but i NEED the Record with ID 2 twice i f select a Date Range from
    29/08/2019 until 30/08/2019, i have to set the first from 22:00 until 24:00 (23:59)
    If i select only 29/08/2019 i have to see this Record once, because is CheckIn Time
    If i select only 30/08/2019 i have to see this Record once, because is CheckOut Time

    I have some Good Experience with MSSQL with complex DB querys and join's but never have been worked with Dates. I was searching Days in Internet but can't find any Good Solution
    Here is my Code i use and gives me some exit but is not the best because if i select a Date Range it shows correct,
    If i select only the CheckIn Date it shows this Date twice, the same if i select the CheckOut Date.

    If somebody can give me some help that would be very nice
    regards
    Uli Rosendahlhere my code i have at the moment

     SQL = "select CONVERT(VARCHAR(10), CheckIn, 103) as UserDateIn,"  '** don't touch, works ***
            SQL = SQL & " CONVERT(VARCHAR(8), CheckIn, 108) as UserTimeIn,"
            SQL = SQL & " CONVERT(VARCHAR(10), CheckOut, 103) as UserDateOut,"
            SQL = SQL & " CONVERT(VARCHAR(8), CheckOut, 108) as UserTimeOut,"
            SQL = SQL & " ID, RegisterType_ID, UserID"
            SQL = SQL & " from CheckInOut_Table"
            SQL = SQL & " Where UserID = '" & Val(bezUserID.Text) & "'"
            SQL = SQL & " And CONVERT(DATE, CheckIn) between CONVERT(DATE,'" & dAktDayIn & "')"
            SQL = SQL & " And CONVERT(DATE,'" & dAktDayOut & "')"
            SQL = SQL & " Or CONVERT(VARCHAR(10), CheckIn, 103) <>  CONVERT(VARCHAR(10), CheckOut, 103)"
            '*****************************************************
            SQL = SQL & " UNION All"
            SQL = SQL & " select CONVERT(VARCHAR(10), CheckIn, 103) as UserDateIn,"
            SQL = SQL & " CONVERT(VARCHAR(8), CheckIn, 108) as UserTimeIn,"
            SQL = SQL & " CONVERT(VARCHAR(10), CheckOut, 103) as UserDateOut,"
            SQL = SQL & " CONVERT(VARCHAR(8), CheckOut, 108) as UserTimeOut,"
            SQL = SQL & " ID, RegisterType_ID, UserID"
            SQL = SQL & " from CheckInOut_Table"
            SQL = SQL & " Where UserID = '" & Val(bezUserID.Text) & "'"
            SQL = SQL & " And CONVERT(VARCHAR(10), CheckOut, 103) > CONVERT(DATE,'" & dAktDayIn & "')"
            SQL = SQL & " And CONVERT(VARCHAR(10), CheckIn, 103) <>  CONVERT(VARCHAR(10), CheckOut, 103)"
            '*****************************************************
            SQL = SQL & " Order By UserDateIn, UserTimeIn"

     

    Friday, August 30, 2019 8:21 AM

All replies

  • First, I feel obliged to point that you should never write code where you concatenate parameter values into the query string. That is a total no-no in my book. There are all sorts of issues with this, of which SQL injection is the most important in the grand scheme of this.
    Instead you should use a parameterised query. The exact form depends on what client API you are using. If you are using .NET would be:

     SQL = "select CONVERT(VARCHAR(10), CheckIn, 103) as UserDateIn,"         SQL = SQL & " CONVERT(VARCHAR(8), CheckIn, 108) as UserTimeIn,"
            SQL = SQL & " CONVERT(VARCHAR(10), CheckOut, 103) as UserDateOut,"
            SQL = SQL & " CONVERT(VARCHAR(8), CheckOut, 108) as UserTimeOut,"
            SQL = SQL & " ID, RegisterType_ID, UserID"
            SQL = SQL & " from CheckInOut_Table"
            SQL = SQL & " Where UserID = @userID
            SQL = SQL & " And CONVERT(DATE, CheckIn) between CONVERT(DATE, @dayin)"
            SQL = SQL & " And CONVERT(DATE, @dayout)"
            SQL = SQL & " Or CONVERT(VARCHAR(10), CheckIn, 103) <>  CONVERT(VARCHAR(10), CheckOut, 103)"

    And the you use cmd.Parameters.Add to define the parameters.

    As for your actual question, here is a solution. The idea is to cross join with a small table with the values 1 and 2, and use that to split the ranges. It will however not work if checkin/out ranges can span several days.

    When querying for a date interval, the technique is as I do below. You don't use between but >= the first date and then < the day after the last date in the interval.

    SET DATEFORMAT dmy

    CREATE TABLE #dates (id int NOT NULL, indate datetime2(0) NOT NULL, outdate datetime2(0) NOT NULL)
    INSERT #dates(id, indate, outdate)
    VALUES
    (1,     '29/08/2019 10:00', '29/08/2019 14:00'),
    (2,     '29/08/2019 22:00', '30/08/2019 06:00'),
    (3,     '30/08/2019 10:00', '30/08/2019 14:00')
    go
    SELECT * FROM #dates
    go
    DECLARE @first date = '20190828',
            @second date = '20190829'
    ; WITH splitdates AS (
       SELECT DISTINCT id,           CASE WHEN n = 1 THEN indate
                   WHEN datediff(DAY, indate, outdate) = 0 THEN indate
                   ELSE  convert(date, outdate)            END AS indate,
              CASE WHEN n = 2 THEN outdate
                   WHEN datediff(DAY, indate, outdate) = 0 THEN outdate
                   ELSE dateadd(ss, -1, convert(datetime2(7), convert(date, outdate)))
              END AS outdate
       FROM   #dates d
       CROSS  JOIN (VALUES(1), (2)) AS N(n)
    )
    SELECT id, indate, outdate
    FROM   splitdates
    WHERE  indate  >= @first
      AND  indate  < dateadd(day, 1, @second)
    go
    DROP TABLE #dates


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, August 30, 2019 9:20 AM
  • Hello

    Thanks for your profesional reply.
    The Checkin/Out per Record Will not span more the 2 dates, but the table Will have a lot recordes with the same Scheme, because can be people must work during a period of a month from 22:00 until 06:00 next day.

    then i need to make a query from the first until the last day of the month to get this results.
    I need each record twice if it is over midnight, because can be startday is fryday and end day is saturday so the payment for this hours Will be different.

    I Will try your solution, thanks a lot for this

    regards

    Uli Rosendahl

    Friday, August 30, 2019 10:13 AM
  • The way to do what you want is to use a calendar table to find all the dates between the checkin and checkout dates

    https://weblogs.sqlteam.com/dang/2010/07/19/calendar-table-and-datetime-functions/

    DECLARE @startdate date, @enddate date;
    SET @startdate = '2019-08-29';
    SET @enddate = '2019-08-30';
    
    
    
    DECLARE @data TABLE (ID INT, CheckIn VARCHAR(50), CheckOut VARCHAR(50));
    
    INSERT INTO @data (ID, CheckIn, CheckOut) VALUES 
    (1,'29/08/2019 10:00','29/08/2019 14:00'),
    (2,'29/08/2019 22:00','30/08/2019 06:00'),
    (3,'30/08/2019 10:00','30/08/2019 14:00')
    
    SELECT d.*, c.CalendarDate as EventDate
    FROM @data d
        INNER JOIN dbo.Calendar c
        ON c.CalendarDate BETWEEN CONVERT(DATE, CheckIn, 103) and CONVERT(DATE, CheckOut, 103)
    
    WHERE c.CalendarDate BETWEEN @startdate and  @enddate
    
    ORDER BY d.ID;
    

    Friday, August 30, 2019 12:11 PM
  • Thanks for reply MS SQL Fórum.
    I tried what you recomed and if i execute directly in SQL Server 2012 it Works, i get Tables, the full and the selected

    I tried with more dates and Works as expected, but i’m not Abel to translate this to VB NET.
    Here my code, if you can Help me please, If you have to charge me for this please let me now
    Execute this in SQL Server 2012 works perfect, thats what you send

    SELECT * FROM CheckInOut_Table
     go 
     DECLARE @first date = '20190818',
             @second date = '20190822'
     ; WITH splitdates AS (
        SELECT DISTINCT id, UserID,          CASE WHEN n = 1 THEN CheckIn
                    WHEN datediff(DAY, CheckIn, CheckOut) = 0 THEN CheckIn
                    ELSE  convert(date, CheckOut)            END AS CheckIn,
               CASE WHEN n = 2 THEN CheckOut
                    WHEN datediff(DAY, CheckIn, CheckOut) = 0 THEN CheckOut
                    ELSE dateadd(ss, -1, convert(datetime2(7), convert(date, CheckOut)))
               END AS CheckOut
        FROM   CheckInOut_Table d
        CROSS  JOIN (VALUES(1), (2)) AS N(n)
     )
     SELECT id, CheckIn, CheckOut
     FROM   splitdates
     WHERE  CheckIn  >= @first
       AND  CheckIn  < dateadd(day, 1, @second)
        And UserID = 6
     go
    

    Here my code from VB Net, i get only the full List which i do not need.

    SQL = " SELECT * FROM CheckInOut_Table"
            SQL = SQL & " DECLARE @first date = '20190818'"    '" & dAktDayIn & "'"
            SQL = SQL & " ,@second date = '20190820'"            '" & dAktDayOut & "'"
    
            SQL = SQL & " ; WITH splitdates AS ("
            SQL = SQL & " SELECT DISTINCT id, UserID,  CASE WHEN n = 1 THEN CheckIn"
            SQL = SQL & " WHEN datediff(DAY, CheckIn, CheckOut) = 0 THEN CheckIn"
            SQL = SQL & " ELSE  convert(date, CheckOut) END AS CheckIn,"
            SQL = SQL & " CASE WHEN n = 2 THEN CheckOut"
            SQL = SQL & " WHEN datediff(DAY, CheckIn, CheckOut) = 0 THEN CheckOut"
            SQL = SQL & " ELSE dateadd(ss, -1, convert(datetime2(7), convert(date, CheckOut)))"
            SQL = SQL & " END AS CheckOut"
    
            SQL = SQL & " FROM  CheckInOut_Table d"
            SQL = SQL & " CROSS  JOIN (VALUES(1), (2)) AS N(n)"
            SQL = SQL & ")"
    
            SQL = SQL & " SELECT ID, CheckIn, CheckOut"
            SQL = SQL & " FROM splitdates"
    
            SQL = SQL & " WHERE  CheckIn  >= @first"
            SQL = SQL & " AND  CheckIn  < dateadd(day, 1, @second)"
            SQL = SQL & " AND UserID = 6"      '" & Val(bezUserID.Text) & "'"
    
            '**** End Original ***********
            aCmd = New SqlCommand(SQL, ClientConn)
            ClientConn.Open()
            reader = aCmd.ExecuteReader()
    
            Dim nRowCount As Integer = DataGridClients.RowCount
            If reader.HasRows = True Then
                While reader.Read
    
                    If Not IsDBNull(reader.Item("CheckIn".ToString)) Then sUserDateIn = reader.Item("CheckIn".ToString)
                    If Not IsDBNull(reader.Item("CheckOut".ToString)) Then sUserDateOut = reader.Item("CheckOut".ToString)
                    If Not IsDBNull(reader.Item("ID".ToString)) Then sID = reader.Item("ID".ToString)
                    If Not IsDBNull(reader.Item("UserID".ToString)) Then sUserID = reader.Item("UserID".ToString)
    
                    With DataGridClients
                        Application.DoEvents()
    
                        .Rows.Add()
                        .Rows(nRowCount).Cells(0).Value = sID
                        .Rows(nRowCount).Cells(1).Value = sUserID
                        .Rows(nRowCount).Cells(2).Value = sUserDateIn
                        .Rows(nRowCount).Cells(3).Value = sUserDateOut
                        nRowCount = nRowCount + 1
    
                    End With
                End While
            End If
    
            reader.Close()
            reader = Nothing
            aCmd.Dispose()
            ClientConn.Close()
    

    Please help me to make this running with VB Net
    Thanks in advanced

    Uli Rosendahl



    Saturday, August 31, 2019 11:25 AM
  • 3It seems that you did not read the first part of my port, or you simply ignored it. That was the most important point of my reply.

    Here is how your code should look like:

    SQL =  " ; WITH splitdates AS ("
    SQL = SQL & " SELECT DISTINCT id, UserID,  CASE WHEN n = 1 THEN CheckIn"
    SQL = SQL & " WHEN datediff(DAY, CheckIn, CheckOut) = 0 THEN CheckIn"
    SQL = SQL & " ELSE  convert(date, CheckOut) END AS CheckIn,"
    SQL = SQL & " CASE WHEN n = 2 THEN CheckOut"
    SQL = SQL & " WHEN datediff(DAY, CheckIn, CheckOut) = 0 THEN CheckOut"
    SQL = SQL & " ELSE dateadd(ss, -1, convert(datetime2(7), convert(date, CheckOut)))"
    SQL = SQL & " END AS CheckOut"

    SQL = SQL & " FROM  CheckInOut_Table d"
    SQL = SQL & " CROSS  JOIN (VALUES(1), (2)) AS N(n)"
    SQL = SQL & ")"

    SQL = SQL & " SELECT ID, CheckIn, CheckOut"
    SQL = SQL & " FROM splitdates"

    SQL = SQL & " WHERE  CheckIn >= @first"
    SQL = SQL & " AND  CheckIn  < dateadd(day, 1, @second)"
    SQL = SQL & " AND UserID = @userid"
    aCmd = New SqlCommand(SQL, ClientConn)
    aCmd.Parameters.Add("@first", SqlDBTypes.Date).Value = dAktDayIn
    aCmd.Parameters.Add("@second", SqlDBTypes.Date).Value = dAktDayOut
    aCmd.Parameters.Add("@userid", SqlDBType.????).Value = bezUserId
    ClientConn.Open()
    reader = aCmd.ExecuteReader()

    I've put question marks for the @userid parameters, since I don't know the data type. If the type is int the line should read:

    aCmd.Parameters.Add("@userid", SqlDBType.Int).Value = bezUserId

    If the type is, say, varchar(30), the line should read

    aCmd.Parameters.Add("@userid", SqlDBType.VarChar, 30).Value = bezUserId

    It cannot be overstressed how important it is to user parameterised statements. It is entirely unacceptable to inline parameter values in query strings if you are writing code for professional use.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Sunday, September 1, 2019 7:47 PM