Doubt with sql query using time interval

Navigation
0

I have a Job in the sql server that runs twice a day, 6:00 p.m. and 6:00 p.m., it has an sql query where I get the process date and compare it to the date of the day. It turns out that if you have a new change after 6:00 PM, they will not be updated because there will be a new date after 12:00 AM. How could I offset this time in hours in the query by adding this result? I could change the schedule of the job plus the client thinks not, that I wanted another solution, I can not see a solution. The c.dtHrProcess field has the date in the format: 2016-11-23 04: 38: 10,307 

Segue a consulta 
SELECT DISTINCT p.codInterno,
                CASE
                    WHEN f.idFabricante IS NULL THEN 99999999999999
                    ELSE f.idFabricante
                END AS fabri,
                '0', 
                p.descResumida, 
                p.descProduto,
                p.descProduto,
                p.codElemento,
                GETDATE(),
                GETDATE(),
                0,
                0  
    FROM    [192.168.200.87].[siac].[dbo].[IntPluProduto] p
        INNER JOIN  [192.168.200.98].[siac].[dbo].[IntControle] c on c.idProcesso = p.idProcesso
        LEFT JOIN [192.168.0.150].[tlmark].[dbo].[Produto] vanProduto on vanProduto.CdPrdSAP = p.codInterno
        LEFT JOIN tbFabricante f on f.idFabricante = CAST(vanProduto.CdFbr AS INT)
    WHERE  (select COUNT(*) from tbProduto where idSAP = p.codInterno) = 0
    AND vanProduto.cdFbr <> 'NNN0'
    AND CAST(c.dtHrProcesso AS DATE) = CAST(GETDATE() AS DATE)
    AND c.codModulo = 14
    AND vanProduto.ICMSubst <> 'T'
    
asked by anonymous 27.12.2016 / 13:33

1 answer

1

Change the JOB query that is set to run at 06:00 to filter the current date - 1: 

CAST(c.dtHrProcesso AS DATE) > DATEADD(ss, 64800, DATEADD(d, -1, CAST(CONVERT(varchar(10), GETDATE(), 103) AS smalldatetime))) OR CAST(c.dtHrProcesso AS DATE) = CAST(GETDATE() AS DATE)

The above filter does nothing but, starting from the current date, subtracts 1 day, and after adding 18 hours (64800 seconds).

For example, for the current date 12/27/2016 06:00:00:

  • Subtract 1 day: 12/26/2016 06:00:00
  • Format the result for: 12/26/2016 00:00:00
  • Add 64800 seconds (18hrs): 12/26/2016 6:00 PM

Another way to do it and what I would use it would be to execute each JOB, update a table with the date / time of the last JOB execution.

For example, create a JobAtu table, with a jobData field (DATETIME). When running JOB, update the date field to the current date / time: 

UPDATE JobAtu SET jobData = GETDATE()

Whenever JOB is executed, instead of using the clause: 

CAST(c.dtHrProcesso AS DATE) = CAST(GETDATE() AS DATE)

use: 

CAST(c.dtHrProcesso AS DATE) >= SELECT ISNULL(jobData, CAST(GETDATE() AS DATE)) FROM JobAtu
    
27.12.2016 / 14:01
Do I need to enable Cross-Origin Requests for ASP.NET Web API? apply css property directly by javascript and a good practice? [closed]