Result set without the where clause shows there is data from the 30th September, when using the below where clause the 30th data doesn't come through in the result set (see images below for result sets) - my main aim is to show data from between the first day of the previous month and the last day of the previous month - Any help is much appreciated:

SQL Query

DECLARE @date1 datetime
DECLARE @date2 datetime
SET @date1 = getdate()
SET @date2 = getdate()-15 -- reason for less 15 days is because this will only be run within the first 15 days 
                          -- of the current month, it will enable me to get the last day of the previous 
                          -- month even if I run the SQL in the next month.

SELECT
        A.TA_SEQ as 'WO No',
        A.TA_DUE_DATEUTC

FROM 
        F_TASKS A WITH (NOLOCK)
        INNER JOIN FINFILE B WITH (NOLOCK) ON A.TA_FKEY_IN_SEQ = B.IN_SEQ 
        INNER JOIN InstructionSetGroups C WITH (NOLOCK) ON B.InstructionSetGroupId = C.InstructionSetGroupId 

WHERE
        A.TA_TASK_DESC = 'BREAKDOWN' AND
        A.TA_STATUS IN ('ACTIVE', 'ASSIGNED', 'COMPLETE', 'HISTORY') AND
        A.TA_DUE_DATE >= DATEADD(DAY, 1, EOMONTH(@date1, -2)) AND
        A.TA_DUE_DATE <= EOMONTH(@date2) 

ORDER BY 
        A.TA_DUE_DATE desc

Result Sets

Result set using date ranges in where clause:

Result set without using date ranges in where clause - as you can see there's lots of data from the 30th too which isn't being captured

CodePudding user response：

I would take a different approach to handling your date values. That whole -15 stuff is bizarre and will cause issues on days towards the end of the month. Instead using a single variable is sufficient if you understand some date math.

DECLARE @date1 date --use the data datatype here because time is important in this case
SET @date1 = getdate()

--this is to show you the values and can be removed.
select BeginningOfLastMonth = dateadd(month, datediff(month, 0, @date1) - 1, 0)
    , BeginningOfCurrentMonth = dateadd(month, datediff(month, 0, @date1), 0)

--Your select statement here
where A.TA_DUE_DATE >= dateadd(month, datediff(month, 0, @date1) - 1, 0)
        A.TA_DUE_DATE <= dateadd(month, datediff(month, 0, @date1), 0) --ANY datetime value less than this is before the end of last month

CodePudding user response：

EOMONTH is terribly ambiguous, because it returns the last day of the month at midnight. I talk about why I don't like this function in Will you use EOMONTH()?

If you want the whole previous month, there are easier and safer ways than assuming the code will run within the first 15 days:

DECLARE @ThisMonth date = DATEFROMPARTS(YEAR(GETDATE()), MONTH(GETDATE()), 1);

SELECT ... FROM ...
  WHERE TA_DUE_DATE >= DATEADD(MONTH, -1, @ThisMonth)
    AND TA_DUE_DATE <  @ThisMonth;

I explain why DATEFROMPARTS is best suited for this kind of work in Simplify Date Period Calculations in SQL Server.

With the rest, where do we begin?

• getdate()-15 is lazy, unintuitive shorthand that will break in other contexts. Please use DATEADD(DAY, -15, GETDATE()) and see Bad Habits to Kick : Using shorthand with date/time operations.

• You can declare/set in a more readable way than an explicit line for each declare and again for each set:

   DECLARE @date1 datetime = GETDATE(),
           @date2 datetime = DATEADD(DAY, -15, GETDATE());
  

• as 'WO No' - please use AS [Wo No] or AS "Wo No" - string delimiters make those aliases look like, well, strings, and some forms of that syntax are deprecated.

• please always specify the schema and never use meaningless aliases like A, B, C.

• >= AND <= is the same as BETWEEN. I talk about why BETWEEN is always a terrible idea for date ranges in What do BETWEEN and the devil have in common?

• Lots of resources on NOLOCK in I'm using NOLOCK; is that bad?

• General date tips in Dating responsibly