Following on from the previous post about updating records one data type that will be a little awkward to work with is dates.
If you use a US locale or any other that uses a date format of Month/Day/Year you can more or less ignore this because your standard formats work OK.
If I run
PS> Get-AccessData -sql "select * from test1" -connection $db
FirstName LastName DOB
——— ——– —
Fred … Green … 01/09/1979 00:00:00
John … Green … 01/07/1981 00:00:00
James … Jones … 18/04/1981 00:00:00
In the first record the date is first of September 1979 because I am using a UK date format of day/month/year
if I try to query on that date
PS> Get-AccessData -sql "select * from test1 where DOB = #01/09/1979#" -connection $db
I don’t get anything returned but if I use
PS> Get-AccessData -sql "select * from test1 where DOB = #09/01/1979#" -connection $db
FirstName LastName DOB
——— ——– —
Fred … Green … 01/09/1979 00:00:00
I get the data I expect.
This is a PowerShell issue.
PS> $host
Name : ConsoleHost
Version : 2.0
InstanceId : 98b88ef9-297c-46a8-bc2c-8cbb4cb273df
UI : System.Management.Automation.Internal.Host.InternalHostUserInterface
CurrentCulture : en-GB
CurrentUICulture : en-US
PrivateData : Microsoft.PowerShell.ConsoleHost+ConsoleColorProxy
IsRunspacePushed : False
Runspace : System.Management.Automation.Runspaces.LocalRunspace
Notice that the CurrentUICulture is en-US but my CurrentCulture is en-GB. This means that when I input dates at the PowerShell prompt or in scripts they will be interpreted following the US format.
My recommendation for dealing with this is to use this format
PS> Get-AccessData -sql "select * from test1 where DOB = #01-Sep-79#" -connection $db
FirstName LastName DOB
——— ——– —
Fred … Green … 01/09/1979 00:00:00
This removes the ambiguity and leaves us able to apply the same commands anywhere (at least if you are using English :-))
Our update command then becomes
Set-AccessData -table test1 -filter "DOB = #01-Sep-1979#" -value "DOB = #02-Sep-1979#" -connection $db