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)
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
ERROR:
****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.
Thanks,
JB
$d=(Get-Date).adddays(-90)
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.SqlC
$SqlConnection.ConnectionS
#Instantiates the Command Object
$SqlCmd = New-Object System.Data.SqlClient.SqlC
#Command Text Using Variable
$SqlCmd.CommandText = $SqlQuery
#Create Connection
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlD
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
#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
$SqlConnection.Close()
$d=(Get-Date).adddays(-90)
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
ERROR:
****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.
Thanks,
JB
$d=(Get-Date).adddays(-90)
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.SqlC
$SqlConnection.ConnectionS
#Instantiates the Command Object
$SqlCmd = New-Object System.Data.SqlClient.SqlC
#Command Text Using Variable
$SqlCmd.CommandText = $SqlQuery
#Create Connection
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlD
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
#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
$SqlConnection.Close()
Try converting your reference date
I think I've something something similar for MySQL before.
From this:
$d=(Get-Date).adddays(-90)
To this:
$d = (Get-Date).adddays(-90).ToString('yyyy-MM-dd HH:mm:ss')I think I've something something similar for MySQL before.
ASKER
Coraxal and lcohan:
I tried: $d = (Get-Date).adddays(-90).to
Using $d = (Get-Date).adddays(-90).To
As did:
$d = (Get-Date).adddays(-90).To
$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?
I tried: $d = (Get-Date).adddays(-90).to
Using $d = (Get-Date).adddays(-90).To
As did:
$d = (Get-Date).adddays(-90).To
$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()ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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'"
Never mind, Qlemo has already posted the same suggestion..Next time I will refresh the page before posting the comment.. :-)
ASKER
Thanks again Qlemo.
Gonna have to see about putting you on retainer. ;^)
Gonna have to see about putting you on retainer. ;^)
I'm sooo happy I have been faster than Subsun this time :p
Guess I forgot to take my Superman pill this morning... ;-)
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)