How can I get a between dates data from using month no and year in SQL

Question

0.00/5 (No votes)

See more:

Thanks in advance !!! Now I am Passing the Month and Year in my sql query...It should filter the data by From and to date in sql. For example : I am passing for april month and year 2021 means it should take only 1st april records not get the second april records. I need all the records from april month.

Filteration Query for april month :

(@FromMonth=0 OR dateadd(month,@FromMonth - 1,convert(datetime,convert(varchar,(@Year))))
between SpecialDeduction.Fromdate and SpecialDeduction.ToDate)

What I have tried:

SELECT OrgaMaster.Extension AS OrgID,UserProfile.UnitID,SpecialDeduction.ID, SpecialDeduction.description,
SpecialDeduction.Amount,SpecialDeduction.Fromdate as EffectiveFrom,SpecialDeduction.todate as EffectiveTo,
Case when SpecialDeduction.Status=1 Then 'Active' Else 'InActive' End As Status,
SpecialDeductionCategory.Category,Userprofile.Staffno,UserProfile.empfullname AS EmployeeName,
CASE WHEN UserProfile.isactive = '1' THEN 'Yes' ELSE 'No' END AS IsActive
FROM SpecialDeductionCategory INNER JOIN SpecialDeduction ON SpecialDeductionCategory.ID = SpecialDeduction.CategoryID
INNER JOIN UserProfile ON SpecialDeduction.UserID=UserProfile.UserID
inner join OrgaMaster on UserProfile.UnitId = OrgaMaster.OrgaID
Where OrgaMaster.Level=1
AND ('ALL'='ALL' OR OrgaMaster.Extension ='ALL')
AND ('' ='' OR UserProfile.UnitId='')
AND (null is null OR UserProfile.UserID=null)
AND ( NULL IS NULL OR SpecialDeduction.FromDate BETWEEN NULL AND NULL oR SpecialDeduction.ToDate BETWEEN NULL AND NULL)
--AND (@FromMonth =0 OR @FromMonth BETWEEN month(SpecialDeduction.Fromdate) and month( SpecialDeduction.ToDate) )
and (4 =0 OR dateadd(month,4 - 1,convert(datetime,convert(varchar,(2021))))
between SpecialDeduction.Fromdate and SpecialDeduction.ToDate)

Posted 1-May-21 18:47pm

Member 15028582

Updated 1-May-21 19:46pm

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

OriginalGriff · Answer 1 · 2021-05-01T19:29:00

That looks like someone didn't know what they were doing, and just guessed - throwing conditions together at random and not thinking about what they were doing.
Which is a very bad idea.
I'm not going to pick my way through that trying to work out what is what and where, but when you see things like this:

SQL

AND ('ALL'='ALL' OR ...

Or this:

SQL

AND ('' ='' OR ...

Or this:

SQL

AND (null is null OR ...

Or this:

SQL

AND ( NULL IS NULL OR ...

Or this:

SQL

OR SpecialDeduction.FromDate BETWEEN NULL AND NULL ...

Or this:

SQL

oR SpecialDeduction.ToDate BETWEEN NULL AND NULL) ...

Or this:

SQL

and (4 =0 ...

And that last one ... definitely isn't going to do anything useful...

Stop and think about what you are trying to do, then work out what your selection criteria should be before you go anywhere near a keyboard - because whatever it is you are trying to do (and we have no idea of what that is, or what your data looks like) I'm pretty sure that isn't going to be even close!

Wendelius · Answer 2 · 2021-05-01T19:46:00

To add to the previous answer, why split the date when using it in conditions. instead of comparing month and day separately it would be easier to compare the whole date.

Consider the following example

SQL

begin
   declare @startdate date;
   set @startdate = '04/01/2021';

   select * 
   from tablename
   where @startdate >= somedatecolumn
   and @startdate < dateadd(month, 1, somedatecolumn)
end

How can I get a between dates data from using month no and year in SQL

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0