Filter query base on date field

Transact-SQL
1

Hi All.
I would like to filter query base on date field. That is my approach:

SELECT
    j.Job_Id,
    j.JobID,
    Left([Category],3) AS Cat,
    j.JobName,
    Execution1.Status AS Status1,
    Execution1.CompBy AS CompBy1,
    Execution1.QA AS QA1,
    Execution1.MCompDate AS CompDate1
FROM Jobs j
LEFT JOIN Categories c ON j.Category_Id = c.Category_Id
LEFT JOIN JobNameGroups jng ON j.Job_Id = jng.Job_Id
LEFT JOIN JobTypeGroups jtg ON jng.JobTypeGroup_Id = jtg.JobTypeGroup_Id
LEFT JOIN JobType jt ON jtg.JobType_Id = jt.JobType_Id
LEFT JOIN (
        SELECT
            MonthlyExecs.JobNameGroup_Id,
            MonthlyExecs.ExecNo,
            Status.Status,
            e1.Init AS CompBy,
            e2.Init AS QA,
            MonthlyExecs.MCompDate
        FROM MonthlyExecs
        LEFT JOIN Status ON MonthlyExecs.Status_Id = Status.Status_Id
        LEFT JOIN Employees AS e1 ON MonthlyExecs.Emp_Id = e1.Emp_Id
        LEFT JOIN Employees AS e2 ON MonthlyExecs.QA_Id = e2.Emp_Id
        WHERE MonthlyExecs.ExecNo = 1) AS Execution1
ON jng.JobNameGroup_Id = Execution1.JobNameGroup_Id
WHERE jt.JobType = 'Monthly' AND DATEPART(Month, Execution1.MCompDate) = DATEPART(MONTH, GETDATE())

It returns me nothing. But when I do like this way:

SELECT
    j.Job_Id,
    j.JobID,
    Left([Category],3) AS Cat,
    j.JobName,
    Execution1.Status AS Status1,
    Execution1.CompBy AS CompBy1,
    Execution1.QA AS QA1,
    Execution1.MCompDate AS CompDate1
FROM Jobs j
LEFT JOIN Categories c ON j.Category_Id = c.Category_Id
LEFT JOIN JobNameGroups jng ON j.Job_Id = jng.Job_Id
LEFT JOIN JobTypeGroups jtg ON jng.JobTypeGroup_Id = jtg.JobTypeGroup_Id
LEFT JOIN JobType jt ON jtg.JobType_Id = jt.JobType_Id
LEFT JOIN (
        SELECT
            MonthlyExecs.JobNameGroup_Id,
            MonthlyExecs.ExecNo,
            Status.Status,
            e1.Init AS CompBy,
            e2.Init AS QA,
            MonthlyExecs.MCompDate
        FROM MonthlyExecs
        LEFT JOIN Status ON MonthlyExecs.Status_Id = Status.Status_Id
        LEFT JOIN Employees AS e1 ON MonthlyExecs.Emp_Id = e1.Emp_Id
        LEFT JOIN Employees AS e2 ON MonthlyExecs.QA_Id = e2.Emp_Id
        WHERE MonthlyExecs.ExecNo = 1) AS Execution1
ON jng.JobNameGroup_Id = Execution1.JobNameGroup_Id
WHERE jt.JobType = 'Monthly' AND DATEPART(YEAR, MCompDate) = DATEPART(YEAR, GETDATE())

That returns me result of query.
What is wrong in first approach and how to return data for according month without hard coding value?
Thanks

1 Like
2

If your first query does not return any results, it simply means there aren't any MCompDate in the current month

The second query returns result, means there are rows with MCompDate in the current year but not current month. Check your data

3

Keep in mind that we did just start a new month :-). Maybe no data for October yet?

4

I figured out my problem. In the query filter, I specified the current month and I expect to get the result using a random month date value. My mistake. Thanks everyone.