Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 470
  • Last Modified:

Probelm with Concatenating two Parameters in WHERE CLAUSE

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
0
jtrapat1
Asked:
jtrapat1
  • 5
  • 2
  • 2
  • +1
1 Solution
 
Anthony PerkinsCommented:
Something like this:
A.MONTH_FW BETWEEN @StartMonth AND @EndMonth
AND A.YEAR_FW BETWEEN @StartYear AND @EndYear

Open in new window

0
 
Anthony PerkinsCommented:
Never mind that is not going to cut it.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this could do:
WHERE  ( A.YEAR_FW > @StartYear OR ( A.YEAR_FW = @StartYear AND A.MONTH_FW >= @StartMonth  )
   AND ( A.YEAR_FW < @EndYear OR ( A.YEAR_FW = @EndYear AND A.MONTH_FW <= @EndMonth  )

Open in new window

0
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 
HainKurtSr. System AnalystCommented:
what about this :)

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

0
 
HainKurtSr. System AnalystCommented:
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)
0
 
HainKurtSr. System AnalystCommented:
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)
0
 
HainKurtSr. System AnalystCommented:
or this

A.YEAR_FW+A.MONTH_FW/12  between @StartYear + @StartMonth/12 and @EndYear+@EndMonth/12
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Hainkurt: surely your method is logically correct... but won't be able to use indexes on the columns, if any.
0
 
HainKurtSr. System AnalystCommented:
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...
0
 
jtrapat1Author Commented:
thanks-
this was for SSRS report but i had to use varchar instead of datetime format.

thanks
john
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 5
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now