Display records based on selected date range in months within same year?
-
Greetings, How do I ensure that the following code only queries records that are submitted between March and May 2024? If a record has been submitted between the above date range, display 1. Otherwise, display 0. Currently, we have a code that does this but for entire year. In the code below, I left the code that performs this check with alias of thisYear. I left this line of code but commented it out just to show what we have that works except this time, we just want this check to be between March and May 2024. The code: CASE WHEN d.dateCreated BETWEEN DATEFROMPARTS(2024, 3, 1) AND DATEFROMPARTS(2024, 5 + 1, 1) THEN 1 ELSE 0 END as thisYear appears to work sometimes but does not work other times. No errors but wrong results. That code above, temporarily replaces this line of code below as described above.
--CASE WHEN YEAR(d.dateCreated) = YEAR(getdate()) THEN 1 ELSE 0 END as thisYear
Here is the entire code:
SELECT e.Name, e.email, e.emptitle, d.dateCreated, CASE WHEN YEAR(d.dateSubmitted) < YEAR(getdate()) THEN 1 ELSE 0 END as previousYear, --CASE WHEN YEAR(d.dateSubmitted) = YEAR(getdate()) THEN 1 ELSE 0 END as thisYear CASE WHEN d.dateSubmitted BETWEEN DATEFROMPARTS(2024, 3, 1) AND DATEFROMPARTS(2024, 5 + 1, 1) THEN 1 ELSE 0 END as thisYear FROM Employees e INNER JOIN dateDetails d on e.employeeID = d.employeeID WHERE e.employeeID = someIDI have also tried this:
CASE WHEN d.dateSubmitted >= DATEFROMPARTS(2024, 3, 1) AND d.dateSubmitted < DATEFROMPARTS(2024,5 + 1, 1)
Same inconsistent result. I guess my question is why does this work perfectly:
CASE WHEN YEAR(d.dateSubmitted) = YEAR(getdate()) THEN 1 ELSE 0 END as thisYear
but the date range does not work well? Thanks in advance for your help.
-
Greetings, How do I ensure that the following code only queries records that are submitted between March and May 2024? If a record has been submitted between the above date range, display 1. Otherwise, display 0. Currently, we have a code that does this but for entire year. In the code below, I left the code that performs this check with alias of thisYear. I left this line of code but commented it out just to show what we have that works except this time, we just want this check to be between March and May 2024. The code: CASE WHEN d.dateCreated BETWEEN DATEFROMPARTS(2024, 3, 1) AND DATEFROMPARTS(2024, 5 + 1, 1) THEN 1 ELSE 0 END as thisYear appears to work sometimes but does not work other times. No errors but wrong results. That code above, temporarily replaces this line of code below as described above.
--CASE WHEN YEAR(d.dateCreated) = YEAR(getdate()) THEN 1 ELSE 0 END as thisYear
Here is the entire code:
SELECT e.Name, e.email, e.emptitle, d.dateCreated, CASE WHEN YEAR(d.dateSubmitted) < YEAR(getdate()) THEN 1 ELSE 0 END as previousYear, --CASE WHEN YEAR(d.dateSubmitted) = YEAR(getdate()) THEN 1 ELSE 0 END as thisYear CASE WHEN d.dateSubmitted BETWEEN DATEFROMPARTS(2024, 3, 1) AND DATEFROMPARTS(2024, 5 + 1, 1) THEN 1 ELSE 0 END as thisYear FROM Employees e INNER JOIN dateDetails d on e.employeeID = d.employeeID WHERE e.employeeID = someIDI have also tried this:
CASE WHEN d.dateSubmitted >= DATEFROMPARTS(2024, 3, 1) AND d.dateSubmitted < DATEFROMPARTS(2024,5 + 1, 1)
Same inconsistent result. I guess my question is why does this work perfectly:
CASE WHEN YEAR(d.dateSubmitted) = YEAR(getdate()) THEN 1 ELSE 0 END as thisYear
but the date range does not work well? Thanks in advance for your help.
-
You didn't say what "inconsistent" means. Additionally what is the data type of 'dateSubmitted'?
-
You could have easily told me if my code was right or wrong. Anyway, I have resolved it. I was actually coming here to delete the thread.
You'd be better share your solution.
-
You could have easily told me if my code was right or wrong. Anyway, I have resolved it. I was actually coming here to delete the thread.
samflex wrote:
I was actually coming here to delete the thread.
Please don't. Removing some part and leaving an orphaned thread makes it difficult to know what the original question was. And many of the replies do not make sense. Just update your question with the solution you found and add the word "Solved" to the title.
-
You could have easily told me if my code was right or wrong. Anyway, I have resolved it. I was actually coming here to delete the thread.
samflex wrote:
me if my code was right or wrong.
I wasn't sure without the information I asked for. However I can recognize that mixing date/time/stamp can be problematic. As well as not accounting for what time zone means both for programming and display.
-
samflex wrote:
I was actually coming here to delete the thread.
Please don't. Removing some part and leaving an orphaned thread makes it difficult to know what the original question was. And many of the replies do not make sense. Just update your question with the solution you found and add the word "Solved" to the title.
I wasn't going to delete the thread once someone had responded to it. Here is what ultimately worked for me.
SELECT e.Name, e.email, e.emptitle, d.dateSubmitted,
CASE WHEN YEAR(d.dateSubmitted) < YEAR(getdate()) THEN 1 ELSE 0 END as previousYear,
CASE WHEN d.dateSubmitted >= '20240301' AND d.dateSubmitted < '20240501' THEN 1 ELSE 0 END as thisYear
FROM Employees e
INNER JOIN dateDetails d on e.employeeID = d.employeeID
WHERE e.employeeID = someID -
Greetings, How do I ensure that the following code only queries records that are submitted between March and May 2024? If a record has been submitted between the above date range, display 1. Otherwise, display 0. Currently, we have a code that does this but for entire year. In the code below, I left the code that performs this check with alias of thisYear. I left this line of code but commented it out just to show what we have that works except this time, we just want this check to be between March and May 2024. The code: CASE WHEN d.dateCreated BETWEEN DATEFROMPARTS(2024, 3, 1) AND DATEFROMPARTS(2024, 5 + 1, 1) THEN 1 ELSE 0 END as thisYear appears to work sometimes but does not work other times. No errors but wrong results. That code above, temporarily replaces this line of code below as described above.
--CASE WHEN YEAR(d.dateCreated) = YEAR(getdate()) THEN 1 ELSE 0 END as thisYear
Here is the entire code:
SELECT e.Name, e.email, e.emptitle, d.dateCreated, CASE WHEN YEAR(d.dateSubmitted) < YEAR(getdate()) THEN 1 ELSE 0 END as previousYear, --CASE WHEN YEAR(d.dateSubmitted) = YEAR(getdate()) THEN 1 ELSE 0 END as thisYear CASE WHEN d.dateSubmitted BETWEEN DATEFROMPARTS(2024, 3, 1) AND DATEFROMPARTS(2024, 5 + 1, 1) THEN 1 ELSE 0 END as thisYear FROM Employees e INNER JOIN dateDetails d on e.employeeID = d.employeeID WHERE e.employeeID = someIDI have also tried this:
CASE WHEN d.dateSubmitted >= DATEFROMPARTS(2024, 3, 1) AND d.dateSubmitted < DATEFROMPARTS(2024,5 + 1, 1)
Same inconsistent result. I guess my question is why does this work perfectly:
CASE WHEN YEAR(d.dateSubmitted) = YEAR(getdate()) THEN 1 ELSE 0 END as thisYear
but the date range does not work well? Thanks in advance for your help.
#include #include //Whence data //asynchronous programming