We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

Probelm with Concatenating two Parameters in WHERE CLAUSE

jtrapat1
jtrapat1 asked
on
Medium Priority
478 Views
Last Modified: 2012-05-11
Hi= my database doesnt have the best structure but I cant modify the tables.
Heres my problem:
I have four parameters coming into my query:
startMonth (varchar(2))
startYear (varchar(10)
endMonth (varchar(2)
endYear varchar(10)
sample data is:
4 2011
9 2010
4 2010
So, in my WHERE CLAUSE I need the following since my users are selecting from four string type drop downs:
WHERE
--
A.MONTH_FW IN (@StartMonth,@EndMonth  )
AND
A.YEAR_FW IN (@StartYear ,@EndYear )
-this doesnt work since the months and years could be mismatched:
i.e., 4 2011,
9,2011
Anyway can some one recommend the best way to match these two date oarameters to the database columns?
I tried where (a.month_fw + a.year_fw)  but I dinnt get the right results
Thanks
John
Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2012

Commented:
Something like this:
A.MONTH_FW BETWEEN @StartMonth AND @EndMonth
AND A.YEAR_FW BETWEEN @StartYear AND @EndYear

Open in new window

CERTIFIED EXPERT
Top Expert 2012

Commented:
Never mind that is not going to cut it.
Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
what about this :)

where
A.YEAR_FW*12 between @StartYear*12 + @StartMonth and @EndYear*12+@EndMonth

HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
of course you need to cast the varchars to number first

A.YEAR_FW*12 between cast(@StartYear as int)*12 + cast(@StartMonth as int) and cast(@EndYear as int)*12+cast(@EndMonth as int)
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
oops I forgot to take mount into account...

A.YEAR_FW*12 + A.MONTH_FW  between @StartYear*12 + @StartMonth and @EndYear*12+@EndMonth

(and again you need to cast all varchars to int)
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
or this

A.YEAR_FW+A.MONTH_FW/12  between @StartYear + @StartMonth/12 and @EndYear+@EndMonth/12
Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
Hainkurt: surely your method is logically correct... but won't be able to use indexes on the columns, if any.
HainKurtSr. System Analyst
CERTIFIED EXPERT

Commented:
you are right, I just added my post for fun :) If the table does not have any index (if not, he should add it if he can) it is just other ways to implement the same things...

Author

Commented:
thanks-
this was for SSRS report but i had to use varchar instead of datetime format.

thanks
john
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.