Query as of the current system date

Your query is a little weird in how you use fechaEntrada . On the one hand, it looks like a calculated column, but on the other hand, you use it as if it were a column of a table.

To answer the question, I will assume that fechaEntrada is simply a normal column. And since your query needs to filter by that column, the normal and reasonable thing is that you have defined an index in that column for better performance.

With this in mind, the technique that balances better accuracy, simplicity and good performance (if there is a defined index, will take advantage of it) is, in my opinion, the following:

declare @Hoy datetime = cast(getdate() as date);

select *
  from tbl
 where fechaEntrada >= @Hoy     -- condición inclusiva
   and fechaEntrada < @Hoy + 1  -- condición exclusiva

I am aware that many insist on wanting to use BETWEEN instead of dividing the condition into 2 as I do. Maybe they do it thinking that it should be more efficient to express the filter using a single expression instead of 2, but this is not true.

But as you have noticed, although its performance is good, the inconvenience is that BETWEEN evaluates both deadlines inclusively, which forces you to add the 23:59:59 artificially to the second date to obtain the results desired, which in my opinion is fey and uncomfortable, but also technically is not 100% accurate. For example, what would happen if there was a 2017-12-22 23:59:59.500 date with fractions of a second, which is possible with the datetime type?

Then, there are also other options (such as those found in the other answers) that are definitely valid, and can even be said to be elegant, but require that you include the fechaEntrada column in a function call, such as CONVERT or DATEDIFF . However attractive these options are, they are not ideal for the performance of your query. If you have an index defined in the fechaEntrada column (which would be a good idea), these queries can not take advantage of the index to improve query performance.

I understand that what you are looking for is to select the information corresponding to the current day on a column with date and time information. If this is indeed the case, what I suggest in the first place is to handle a date variable that we will complete with GETDATE() , we do this since GETDATE() is a non-deterministic function, that is, whenever we call it it will have a different date + time , so it is always better to work with a variable initialized at the beginning, let's see:

DECLARE @Hoy DATETIME

SELECT  @Hoy = CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE(), 112))

select c.CodeCompany,
       CONVERT(char(6), @Hoy, 112) AS fechaActual,
       a.idClave,
       c.nombre + ' ' + c.apellidos as Nombre,
       d.Descripcion as Depto, 
       CONVERT (date, @Hoy) fechaEntrada,
       CONVERT (date, @Hoy) fechaSalida,
       DATEDIFF(HOUR, fechaEntrada, fechaSalida) %24 AS horasAlDia
       from tblasistencia a JOIN tblpersonal c ON a.idClave=c.idClave
       JOIN tblDepto d ON c.fkDepto=d.idDepto
       where 
           CONVERT(DATETIME, CONVERT(VARCHAR, fechaEntrada, 112)) = @Hoy

The trick is to convert both the current date and the date of the table into dates without the time part, doing CONVERT(DATETIME, CONVERT(VARCHAR, fechaEntrada, 112)) and just compare both.

If you want to consider only dates that have the same day but without the hours, you could use DATEDIFF .

For example:

SELECT
    DATEDIFF(DD, '20171223 03:29:03', '20171223 03:29:03')
    ,DATEDIFF(DD, '20171224 03:29:03', '20171223 03:29:03')
    ,DATEDIFF(DD, '20171222 03:29:03', '20171223 03:29:03')
    ,DATEDIFF(DD, '20171223 23:59:59', '20171223 03:29:03')

You get:

+------------------+------------------+------------------+------------------+
| (No column name) | (No column name) | (No column name) | (No column name) |
+------------------+------------------+------------------+------------------+
|                0 |               -1 |                1 |                0 |
+------------------+------------------+------------------+------------------+

DEMO

In your case if you want for the current date, you might consider:

where DATEDIFF(DD, fechaEntrada, GETDATE()) = 0

Reference:

• DATEDIFF Transact-SQL