Click here to Skip to main content
15,042,397 members
Please Sign up or sign in to vote.
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
Updated 1-May-21 19:46pm

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!
   
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
   

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900