Powershell Script: SQL Query Comparing Dates

Within my SQL table I have a date field that lists the date the record was created. I only want to process records where the date is 90 days earlier than the current date.

$d=(Get-Date).adddays(-90)   shows the following format: 10/19/2012 1:07:40 PM

However the table I'm working with has about 8000 records and the date is in this format: 2012-10-19 00:00:00.000

If I use: $SqlQuery = "SELECT UserName from TermEmp_Test WHERE DisableDate<'20012-10-19 00:00:00.000'"      The script works fine.  

Attempting date comparison throws the following:

Old Date: 10/19/2012 1:29:16 PM

ERROR: Exception calling "Fill" with "1" argument(s) "Incorrect Syntax near '13'."
ERROR: At Line 47 char 17
ERROR: + $SqlAdapter.Fill <<<<<($DataSet) | Out-Null
ERROR:      +Category Info                  : Not Specified: (:) [], MethodInvocationException
ERROR:       +Fully Qualified Error ID  : DotNetMethodException

****PowerShell Script Finished. *****

I really need the comparison to work and I'm not sure if it's the difference in the format or the statement itself.



Write-Host "Old Date: " $d

#$SqlQuery = "SELECT UserName from TermEmp_Test WHERE DisableDate<'2012-10-19 00:00:00.000'"

$SqlQuery = "SELECT * from TermEmp_Test WHERE DisableDate<#$d#"

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"

#Instantiates the Command Object
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand

#Command Text Using Variable
$SqlCmd.CommandText = $SqlQuery

#Create Connection
$SqlCmd.Connection = $SqlConnection

$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd

$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet) | Out-Null

#Populate Hash Table
$objTable = $DataSet.Tables[0]

#Populate Hash Table, and export to CSV File
#$DataSet.Tables[0] | Export-Csv -NoTypeInformation $AttachmentPath

#Populate Hash Table, export to CSV, and change column headers
$DataSet.Tables[0] | select @{l='Alias'; e={$_.Username}} | Export-Csv -NoTypeInformation $AttachmentPath

Who is Participating?

Improve company productivity with a Business Account.Sign Up

QlemoConnect With a Mentor Batchelor, Developer and EE Topic AdvisorCommented:
Your are not using
   $SqlQuery = "SELECT * from TermEmp_Test WHERE DisableDate<#$d#"
I hope! MSSQL doesn't like number signs as delimiters. You would have to use single quotes, and the "yyyy-mm-hh" date format:
$d = (Get-Date).adddays(-90).ToString('yyyy-MM-dd')
$SqlQuery = "SELECT * from TermEmp_Test WHERE DisableDate< '$d' "

Open in new window

Providing the time portion of 0:0:0 is optional.
lcohanDatabase AnalystCommented:
"If I use: $SqlQuery = "SELECT UserName from TermEmp_Test WHERE DisableDate<'20012-10-19 00:00:00.000'"      The script works fine."

that means in my opinion that your TermEmp_Test.DisableDate is DATE data type (no time portion) so you would need to convert your $d=(Get-Date).adddays(-90) as DATE not DATETIME format - drop the time portion of it
Try converting your reference date
From this:


To this:

$d = (Get-Date).adddays(-90).ToString('yyyy-MM-dd HH:mm:ss')

Open in new window

I think I've something something similar for MySQL before.
Creating Active Directory Users from a Text File

If your organization has a need to mass-create AD user accounts, watch this video to see how its done without the need for scripting or other unnecessary complexities.

JB4375Author Commented:
Coraxal and lcohan:

I tried: $d = (Get-Date).adddays(-90).toshortdatestring() before posting here, resulting in the same error above but specifically pointing to the '#' on DisableDate<#$d#.

Using $d = (Get-Date).adddays(-90).ToString('yyyy-MM-dd HH:mm:ss') gets the same error that I posted originally.

As did:
$d = (Get-Date).adddays(-90).ToString('yyyy-MM-dd')
$d = $d + " 00:00:00.000"

which looks exactly like what I'm comparing.

Question: Just throwing ideas around but is it possible to select the row of data, and strip the " 00:00:00.000" from the end before doing the comparison?
Just curious...have you tried the .ToShortDateString()?
$d = (Get-Date).AddDays(-90).ToShortDateString()

Open in new window

You can check this and see if it works..
$d = (Get-Date).adddays(-90).tostring("MM-dd-yyyy hh:mm:ss.fff")
$SqlQuery = "SELECT * from TermEmp_Test WHERE DisableDate<'$d'"

Open in new window

Never mind, Qlemo has already posted the same suggestion..Next time I will refresh the page before posting the comment.. :-)
JB4375Author Commented:
Thanks again Qlemo.

Gonna have to see about putting you on retainer.  ;^)
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
I'm sooo happy I have been faster than Subsun this time :p
Guess I forgot to take my Superman pill this morning... ;-)
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.