MSSQL Query between 2 Dates

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"

 

Answer 1

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, [e-mail]

Answer 2

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

Answer 3

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;

Answer 4

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

Answer 5

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, [e-mail]