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

Question

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

aaron.reese

SSChampion

Points: 13498
More actions
April 15, 2024 at 9:26 am

#4411329
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.
April 15, 2024 at 2:20 pm

This was removed by the editor as SPAM

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

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

Phil Parkin SSC Guru Points: 246985 More actions · Answer 1

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.

This reply was modified 8 months, 1 week ago by Phil Parkin.
This reply was modified 8 months, 1 week ago by Phil Parkin.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

aaron.reese SSChampion Points: 13498 More actions · Answer 2

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.

This reply was modified 8 months, 1 week ago by aaron.reese.

ScottPletcher SSC Guru Points: 101120 More actions · Answer 3

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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

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)

Ken McKelvey SSCoach Points: 18856 More actions · Answer 5

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.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

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)

frederico_fonseca SSCoach Points: 16167 More actions · Answer 7

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

Jonathan AC Roberts SSCoach Points: 18361 More actions · Answer 8

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?

Jonathan AC Roberts SSCoach Points: 18361 More actions · Answer 9

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?