Richard Siddaway's Blog

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