Query with sql query using time interval

Question

I have a Job in sql server that runs twice a day, 06:00 and 18:00, it has an sql query where I take the date of the process and compare with the date of the day. It turns out that if you have a new change after 18:00, these will not be updated because it will be a new date after 24:00. How could I make up this time in hours on the query by adding this result? I could change the job schedule but the client thinks no, that I wanted another solution, I can’t 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'

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 27/12/2016 06:00:00:

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

Another way of doing and that I would use, would be to each execution of JOB, update a table with the date/ time of the last execution of JOB.

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

UPDATE JobAtu SET jobData = GETDATE()

Every time JOB is executed, instead of using the clause:

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

utilize:

CAST(c.dtHrProcesso AS DATE) >= SELECT ISNULL(jobData, CAST(GETDATE() AS DATE)) FROM JobAtu