Duplicated post, please delete

• aaron.reese

  SSChampion

  Points: 13498

  More actions

  April 15, 2024 at 9:26 am

  Go to Answer

  #4411328

  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.

  • This topic was modified 11 months, 2 weeks ago by  aaron.reese.

• Phil Parkin

  SSC Guru

  Points: 247109

  More actions

  April 15, 2024 at 9:54 am

  Answer

  #4411349

  Duplicated reply!

  • This reply was modified 11 months, 2 weeks ago by  Phil Parkin.

  ---