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!
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply