Query run from SQL agent, not returning same results as from query window

  • I am running SQL2012 (don't ask...) and my collation is Latin1_General_CI_AS and my language code is 1033 (British English) at both instance and database level.

    I have the following code executing in an SQL Agent job

    INSERT INTO DEV.scratch
    (
    ... List of Fileds
    )
    EXEC [DEV].[ADR_TEST]

    Part of the logic restricts records to only return data up to the end of the previous week

    WHERE
    RL.LedgerDate >=
    (
    SELECT
    MIN(FW.FinWKStart) AS [start]
    FROM
    FinWeek AS FW
    WHERE
    FW.FinYear = @ReportingYear
    )
    /*But don't include transactions in the week the report is run (i.e. only up to last Sunday)*/
    AND
    RL.LedgerDate <=
    (
    SELECT DATEADD(DAY,-1,DATEADD(DAY,DATEPART(WEEKDAY,GETDATE()) * -1, CAST(GETDATE() AS DATE)))
    )

    I am in the UK so Monday  = WEEKDAY = 1.  The financial year in the FinWeek table starts 1st April 2024.  When I run the code from Management Studio in a query window on 15th April, I get 2 weeks of data (2nd week ended 14th April).  When I run the same code from SQL agent job, it only returns the first weeks data, which is an incorrect response.

    I am making the assumption that the SQL agent job is calculating the WEEKDAY differently, and if that is the case, why does it not respect the instance or database settings, and how do I enforce the correct calculation.   If the results are inconsistent for a different reason, I am all ears.

  • I would use the following code to get 'last' Sunday. Seems a bit simpler than the current version.

    SELECT DATEADD (wk, DATEDIFF (wk, 0, GETDATE ()), 0) - 1;

     

    Also, you could try explicitly forcing the issue by running SET DATEFIRST.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • I have resorted to setting the DATEFIRST in the SQL Agent job, but I still want to know WHY SQL agent and Query window behave differently.

  • Just use a method that works under any/all DATEFIRST settings, much simpler and safer:

    /* calc immediately previous Sunday; day 0 = Monday, so day 6 = Sunday */
    SELECT DATEADD(DAY, -DATEDIFF(DAY, 6, GETDATE()) % 7, CAST(GETDATE() AS date))

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

  • Do not use DATEDIFF(WK) if you need for the first of the week to be based on any day of the week other than SUNDAY. It is ALWAYS based on SUNDAY.  Don't take my word for it, though... Please read the 3rd paragraph in the following section of the MS documentation on DATEDIFF...

    https://learn.microsoft.com/en-us/sql/t-sql/functions/datediff-transact-sql?view=sql-server-ver16#remarks

    For those that haven't had enough coffee this morning, the documentation states it, plain and simple, as follows...

    Specifying SET DATEFIRST has no effect on DATEDIFF. DATEDIFF always uses Sunday as the first day of the week to ensure the function operates in a deterministic way.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • aaron.reese wrote:

    I have resorted to setting the DATEFIRST in the SQL Agent job, but I still want to know WHY SQL agent and Query window behave differently.

    Probably because the Agent Job runs on the server which is likely to be set to US defaults.

  • Phil Parkin wrote:

    I would use the following code to get 'last' Sunday. Seems a bit simpler than the current version.

    SELECT DATEADD (wk, DATEDIFF (wk, 0, GETDATE ()), 0) - 1;

    Also, you could try explicitly forcing the issue by running SET DATEFIRST.

    Or, forget the Week datepart.  Use the tried and true method of always having it be right for Mondays (of course, change the 0 appropriately  for other days of the week).

    SELECT FirstMondayCurWeek = DATEADD(dd,DATEDIFF(dd,0,GETDATE())/7*7,0);

    Of course, if you want your entire team to stop messing around and having 72 different versions of such code (and many are actually incorrect), please see the following article...

    https://www.sqlservercentral.com/articles/how-to-find-the-start-and-end-dates-for-the-current-week-and-more

    As a bit of a sidebar, I try to avoid the use of DATEFIRST like the plague because way too much changes if you send your code "overseas" or to have it work remotely in a different company.  It's just not worth it to me. YMMV.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • This was removed by the editor as SPAM

  • one other thing that can cause differences is the timezone of the server and the time /day the job runs on the server.

  • aaron.reese wrote:

    I have resorted to setting the DATEFIRST in the SQL Agent job, but I still want to know WHY SQL agent and Query window behave differently.

    Different users can be set up with different settings. Maybe the Agent user has different settings to the user you are using in the query window?

  • aaron.reese wrote:

    I have resorted to setting the DATEFIRST in the SQL Agent job, but I still want to know WHY SQL agent and Query window behave differently.

    Different users can be set up with different settings. Maybe the Agent user has different settings to the user you are using in the query window?

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply