Powershell SQLQuery criteria ignored with variable

Dear Expert

This is only half of the powershell code, the rest for me is not important to know, mostly a system.data.sqlClient.sqlconnect standard.
The problem however is the variable $AgeFilter, I want the $AgeFilter to go back 64years, 11months, with a format yyyyMM, this result will be: 195304 and then in variable $Sqlquery I used CNumber LIKE '$AgeFilter'% so I thought this means it will show 195304%, well... It does not... what it showed is everything from 194604,195003, like the criteria of the CNumber LIKE... not working, anyone got a idea?

Thanks

$SQLServer = "SQLP04"
$SQLDBName = "System_Prod"
$AgeFilter = ((Get-Date).addyears(-64).AddMonths(-11)).ToString("yyyyMM")

function fquery ($AgeFilter, $SQLDBName){
$SqlQuery = "
SELECT distinct [FirstName],[LastName],[CNumber]
FROM [i_System_Prod].[Contact].[Individual] i
JOIN [r_System_Prod].[Invoice].[Reminder] r on r.HeadId = i.EntryId 
WHERE r.ReminderNumber = '3' AND i.CNumber LIKE '$AgeFilter'%
" 

Open in new window

LVL 1
WeTiAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NorieAnalyst Assistant Commented:
What type of field is CNumber?
0
WeTiAuthor Commented:
Problem solved, I added: 2x variables, first: $sqlsymbol, and $sqlsyntax, see below code, but is there a way to improve this even more?

$SQLServer = "SQLP04"
$SQLDBName = "System_Prod"
$AgeFilter = ((Get-Date).addyears(-64).AddMonths(-11)).ToString("yyyyMM")
$sqlsymbol = "%"
$sqlsyntax = $AgeFilter + $sqlsymbol

function fquery ($sqlsyntax, $SQLDBName){
$SqlQuery = "
SELECT distinct [FirstName],[LastName],[CNumber]
FROM [i_System_Prod].[Contact].[Individual] i
JOIN [r_System_Prod].[Invoice].[Reminder] r on r.HeadId = i.EntryId 
WHERE r.ReminderNumber = '3' AND i.CNumber LIKE '$sqlsyntax'
" 

Open in new window

0
oBdACommented:
If the second query worked for you, then the first should have worked as well if you'd moved the % at the end inside the single quotes.
What you should do with multi-line strings is define them as "Here-Strings", which start with a @" (or @'), and only with a "@ (or '@) at the very beginning of a line. That way, quotes inside the string don't need to be escaped.
Try the query like that (without the additional variables):
$SqlQuery = @"
	SELECT distinct [FirstName],[LastName],[CNumber]
	FROM [i_System_Prod].[Contact].[Individual] i
	JOIN [r_System_Prod].[Invoice].[Reminder] r on r.HeadId = i.EntryId 
	WHERE r.ReminderNumber = '3' AND i.CNumber LIKE '$($AgeFilter)%'
@"

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Challenges in Government Cyber Security

Has cyber security been a challenge in your government organization? Are you looking to improve your government's network security? Learn more about how to improve your government organization's security by viewing our on-demand webinar!

WeTiAuthor Commented:
'$($AgeFilter)%' worked too,  I must remember this that you can do '$(Var)%'...
0
WeTiAuthor Commented:
Tried also: added @ and it works also... now question is which is better way to do, for me maybe this @" and @" is more logical to remember.... However for me it's really no different....  

$SqlQuery = @"
	SELECT distinct [FirstName],[LastName],[CNumber]
	FROM [i_System_Prod].[Contact].[Individual] i
	JOIN [r_System_Prod].[Invoice].[Reminder] r on r.HeadId = i.EntryId 
	WHERE r.ReminderNumber = '3' AND i.CNumber LIKE '$AgeFilter%'
@"

Open in new window

0
oBdACommented:
In this case, it makes no difference. But as soon as you'd need a literal double quote in a string, you'd have to escape it, otherwise it would close the string. Inside a Here-String, you can do whatever you want, without having to think about escaping anything.
So for strings spanning more than one line, I always use Here-Strings.
Similar for the "$($Whatever)" - the $() part is a subexpression, which can even contain Powershell commands, not only a variable.
In this case, the subexpression is not required syntactically, '$AgeFilter%' will work just as well. Subexpressions are required, though, as soon as you want to address an object's property inside a string:
"Temp path is '$ENV:Temp' and has a length of $($ENV:Temp.Length)"
So instead of using subexpressions only when really required, I use them consistently:
"Temp path is '$($ENV:Temp)' and has a length of $($ENV:Temp.Length)"
More about these two here, for example:
PowerShell Team Blog > Variable expansion in strings and here-strings
https://blogs.msdn.microsoft.com/powershell/2006/07/15/variable-expansion-in-strings-and-here-strings/
1
WeTiAuthor Commented:
Thank you very much for the teaching.
0
Qlemo"Batchelor", Developer and EE Topic AdvisorCommented:
Of course the here-string needs to end with "@, not @", as posted twice erroneously.
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Powershell

From novice to tech pro — start learning today.