Try to use something like this:
DECLARE @tInFrom DATETIME
DECLARE @tInTo DATETIME
DECLARE @tOutFrom DATETIME
DECLARE @tOutTo DATETIME
SET @tInFrom = CONVERT(DATETIME, CONVERT(NVARCHAR(10),GETDATE(),120) + ' 00:00:00')
SET @tInTo = CONVERT(DATETIME, CONVERT(NVARCHAR(10),GETDATE(),120) + ' 12:59:59')
SET @tOutFrom = CONVERT(DATETIME, CONVERT(NVARCHAR(10),GETDATE(),120) + ' 13:00:00')
SET @tOutTo = CONVERT(DATETIME, CONVERT(NVARCHAR(10),GETDATE(),120) + ' 23:59:59')
DECLARE @cols NVARCHAR(200)
DECLARE @dt NVARCHAR(1000)
DECLARE @pt NVARCHAR(2000)
SET @cols = '[TimeIn],[TimeOut]'
SET @dt = 'SELECT EMP.FIRSTNAME + '' '' + EMP.LASTNAME AS FULLNAME, MIN(Eventime) AS [EventTime], ''TimeIn'' AS [Description] ' +
'FROM EMP INNER JOIN EVENTS ON EVENTS.EMPID = EMP.ID ' +
'WHERE (EVENTS.EVENTIME BETWEEN ''' + @tInFrom + ''' AND ''' + @tInTo + ''') ' +
'GROUP BY EMP.ID ' +
'UNION ALL ' +
'SELECT EMP.FIRSTNAME + '' '' + EMP.LASTNAME AS FULLNAME, MAX(Eventime) as [EventTime], ''TimeOut'' AS [Description] ' +
'FROM EMP INNER JOIN EVENTS ON EVENTS.EMPID = EMP.ID ' +
'WHERE (EVENTS.EVENTIME BETWEEN ''' + @tOutFrom + ''' AND ''' + @tOutTo + ''') ' +
'GROUP BY EMP.ID '
SET @pt = 'SELECT FULLNAME, ' + @cols + ' ' +
'FROM (' + @dt + ') AS DT ' +
'PIVOT (MAX(EventTime) FOR [Description] IN (' + @cols + ')) AS PT'
EXEC(@pt)
The above example should works with MS SQL Server 2005. Please, read about
Date and Time functions[
^] corresponding with your version of MS SQL Server.