Queries with DATETIME

Question

Queries with DATETIME

Navigation

#1 by (1 votes)

0

I want to return the following values as shown below:

     fechaActual    Clave  Usuario  Depto   entrada     salida       sumaHoras
       201712       307    USER1   TTT      06:00:04  14:04:12          8

The query must take the current date of the system and from this should do the filtering however; the result is different from the above query, on the one hand the sum is correct but does not show the correct entry and exit, the output that shows them is taking part of what is defined in:

SELECT  @Hoy1 = CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE(), 112))+'00:00:00'
SELECT  @Hoy2 = CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE(), 112))+'23:59:59'.

And also because the query is defined in this way:

  CONVERT (VARCHAR(20), @Hoy1,108) fechaEntrada,
  CONVERT (VARCHAR(20), @Hoy2,108) fechaSalida,

Query:

 DECLARE @Hoy DATETIME 
 DECLARE @Hoy1 DATETIME 
 DECLARE @Hoy2 DATETIME 

  SELECT  @Hoy = CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE(), 112))
  SELECT  @Hoy1 = CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE(), 112))+'00:00:00'
  SELECT  @Hoy2 = CONVERT(DATETIME, CONVERT(VARCHAR, GETDATE(), 112))+'23:59:59'



select c.CodeCompany,
CONVERT(char(6), @Hoy, 112) AS fechaActual,
a.idClave,
c.nombre + ' ' + c.apellidos as Nombre,
d.Descripcion as Depto, 
CONVERT (VARCHAR(20), @Hoy1,108) fechaEntrada,
CONVERT (VARCHAR(20), @Hoy2,108) 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
   fechaEntrada BETWEEN CONVERT(DATETIME, @Hoy1,103) and 
        CONVERT(DATETIME, @Hoy2,103)and fkStatus !=1

Result of the query

      fechaActual    Clave   Usuario   Depto                        entrada            salida           sumaHoras

              201712        10120      USER1      TTT                            00:00:00         23:59:59                8

Desired result:

      fechaActual    Clave   Usuario   Depto                                    entrada            salida           sumaHoras                   201712       307    USER1   TTT                             06:00:04           14:04:12               8

Incorrect result:

      fechaActual    Clave   Usuario   Depto                        entrada            salida           sumaHoras

                   201712        10120      USER1      TTT                            00:00:00         23:59:59                8

sql sql-server

asked by Noel L 04.01.2018 в 06:16

source

1 answer

Problem Array to String Conversion Set several components at the same time

score 1 · Accepted Answer

It seems that the problem of incorrect times in the result is simply that you are showing the time of the variables @Hoy1 and @Hoy2 instead of the fields fechaEntrada and fechaSalida .

On the other hand, if you are using dates and you have data with date type, use them as well. You should not be converting dates to text and back to date .... the only thing that will generate errors and performance problems.

Your query might look something like this:

DECLARE @Hoy DATE
SELECT @Hoy = GETDATE()

SELECT c.CodeCompany, CONVERT(char(6), @Hoy, 112) AS fechaActual,
    a.idClave, c.nombre + ' ' + c.apellidos AS Nombre, d.Descripcion AS Depto, 
    CONVERT (VARCHAR(20), fechaEntrada, 108) fechaEntrada,
    CONVERT (VARCHAR(20), fechaSalida, 108) 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 fechaEntrada BETWEEN @Hoy AND DATEADD(DAY, 1, @Hoy) and fkStatus !=1

Personally, I would even avoid converting the results of the fields to a string. If they are date data, return them as such. That is the application that receives the data and that wants to show them (in screen, report, ...) the one that formatee:

DECLARE @Hoy DATE
SELECT @Hoy = GETDATE()

SELECT c.CodeCompany, @Hoy AS fechaActual,
    a.idClave, c.nombre + ' ' + c.apellidos AS Nombre, d.Descripcion AS Depto, 
    fechaEntrada, 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 fechaEntrada BETWEEN @Hoy AND DATEADD(DAY, 1, @Hoy) and fkStatus !=1