Click here to Skip to main content
15,877,246 members
Home / Discussions / Database
   

Database

 
PinnedHOW TO ANSWER A QUESTION PinPopular
Chris Maunder16-Jul-09 3:10
cofounderChris Maunder16-Jul-09 3:10 
PinnedHow to get an answer to your question PinPopular
Chris Maunder10-Nov-05 16:30
cofounderChris Maunder10-Nov-05 16:30 
QuestionRun an external program C# from within the Oracle Developer Pin
Member 162354202-Apr-24 10:37
Member 162354202-Apr-24 10:37 
AnswerRe: Run an external program C# from within the Oracle Developer Pin
jschell2-Apr-24 14:18
jschell2-Apr-24 14:18 
QuestionDisplay records based on selected date range in months within same year? Pin
samflex21-Jan-24 9:28
samflex21-Jan-24 9:28 
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 = someID


I 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.
AnswerRe: Display records based on selected date range in months within same year? Pin
jschell22-Jan-24 5:50
jschell22-Jan-24 5:50 
GeneralRe: Display records based on selected date range in months within same year? Pin
samflex22-Jan-24 8:35
samflex22-Jan-24 8:35 
GeneralRe: Display records based on selected date range in months within same year? Pin
Victor Nijegorodov22-Jan-24 10:10
Victor Nijegorodov22-Jan-24 10:10 
GeneralRe: Display records based on selected date range in months within same year? Pin
Richard MacCutchan22-Jan-24 21:47
mveRichard MacCutchan22-Jan-24 21:47 
GeneralRe: Display records based on selected date range in months within same year? Pin
samflex23-Jan-24 13:44
samflex23-Jan-24 13:44 
GeneralRe: Display records based on selected date range in months within same year? Pin
jschell23-Jan-24 5:55
jschell23-Jan-24 5:55 
Questiontime-consuming insert operation Pin
mike741118-Jan-24 16:25
mike741118-Jan-24 16:25 
AnswerRe: time-consuming insert operation Pin
Eddy Vluggen18-Jan-24 16:41
professionalEddy Vluggen18-Jan-24 16:41 
AnswerRe: time-consuming insert operation Pin
jschell19-Jan-24 4:40
jschell19-Jan-24 4:40 
QuestionCREATE INDEX Pin
mike741117-Jan-24 17:22
mike741117-Jan-24 17:22 
AnswerRe: CREATE INDEX Pin
Victor Nijegorodov17-Jan-24 20:37
Victor Nijegorodov17-Jan-24 20:37 
AnswerRe: CREATE INDEX Pin
jschell18-Jan-24 4:44
jschell18-Jan-24 4:44 
AnswerRe: CREATE INDEX Pin
Eddy Vluggen18-Jan-24 7:38
professionalEddy Vluggen18-Jan-24 7:38 
QuestionSQL behind the scenes Pin
mike741116-Jan-24 17:02
mike741116-Jan-24 17:02 
AnswerRe: SQL behind the scenes Pin
Richard MacCutchan16-Jan-24 21:54
mveRichard MacCutchan16-Jan-24 21:54 
AnswerRe: SQL behind the scenes Pin
Eddy Vluggen16-Jan-24 22:40
professionalEddy Vluggen16-Jan-24 22:40 
AnswerRe: SQL behind the scenes Pin
jschell17-Jan-24 4:29
jschell17-Jan-24 4:29 
AnswerRe: SQL behind the scenes Pin
RedDk17-Jan-24 8:30
RedDk17-Jan-24 8:30 
AnswerRe: SQL behind the scenes Pin
Eddy Vluggen18-Jan-24 7:33
professionalEddy Vluggen18-Jan-24 7:33 
GeneralRe: SQL behind the scenes Pin
jschell19-Jan-24 4:52
jschell19-Jan-24 4:52 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.