Duplicated post, please delete

  • 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.

  • Duplicated reply!

    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.

Viewing 2 posts - 1 through 1 (of 1 total)

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