Convert French date to US

PowerShell Help
1

Hi, I am trying to construct a string with file properties to insert in a table in SQL as follows

select File_name,Fully_Qualified_FileName,attributes, CreationTime, LastAccessTime, LastWriteTime,@{Label="Length";Expression={$_.Length / 1MB -as [int] }}| ForEach-Object { $SQL = $sqlstatement -f $_.FullName, $_.name,$_.attributes, $_.CreationTime, $_.LastAccessTime, $_.LastWriteTime,$_.Length Invoke-sqlcmd -Query $SQL -ServerInstance sql2k12 -database BaselineDB
As my files have all dates in French format and my sql server is US version, some records are not getting entered because it is is in the format dd/mm/yyyy. I created a function to convert French date to US date as follows
function unction changeDataFormat { param( [Parameter(Position=0,mandatory=$true)] [datetime[]] $Dateorig ) $ukString = $Dateorig

$ukCulture = [Globalization.CultureInfo]‘fr-FR’
$usCulture = [Globalization.CultureInfo]‘en-US’

$datetime = [datetime]::Parse($ukString, $ukCulture)

$usString = $datetime.ToString(‘d’, $usCulture)
Write-Host $usString
return $usString
}


but when I try to use it I get a red swiggly line under the function indicating “Unexpected token changeDataFormat in expression or statement”

select File_name,Fully_Qualified_FileName,attributes, CreationTime, LastAccessTime, LastWriteTime,@{Label="Length";Expression={$_.Length / 1MB -as [int] }}| ForEach-Object { $SQL = $sqlstatement -f $_.FullName, $_.name,$_.attributes, changeDataFormat($_.CreationTime), $_.LastAccessTime, $_.LastWriteTime,$_.Length Write-Host $SQL #Invoke-sqlcmd -Query $SQL -ServerInstance sql2k12 -database BaselineDB
2

Please format your code as code using the “pre” tags, thanks.
I think you should wrap your function call in parenthesis with a dollar sign before like this “$(changeDataFormat $_.CreationTime)”

3

So many thanks, yes it is working like a charm. I will make sure to use Pre next time

4

One issue appeared, Creation date retrieved is in the following format and tried when converting to keep it Date and time

13/02/2017 10:04:07

I tried several Format Specifier on the line to convert but noting is getting through, I replaced the d with 'g", G, u…no one is going through

$usString = $datetime.ToString(‘d’, $usCulture)

Thanks

 

5

In your change function you specify to output your datetime as string. Why don’t you simpply return a datetime object. That would be more flexible and as I think more professional.

6

I did , added datetime type in the beginning

[datetime] $usString = $datetime.ToString(‘u’, $usCulture)

and now it is doing the conversion but with 1 hour in advance for the US time

13/02/2017 10:04:07
02/13/2017 11:04:07

13/11/2016 14:23:35
11/13/2016 15:23:35

7

Please ignore my last poste as the format specifier was 'u", I changed it to ‘O’ and all dates are correct now.

Thanks for your help

8

Rather than messing with Culture, you can just tell ParseExact how to parse the date:

$date = '13/02/2017 10:04:07'

[datetime]::ParseExact($date, "dd/MM/yyyy H:mm:ss", [cultureinfo]::InvariantCulture)
#or
[datetime]::ParseExact($date, "dd/MM/yyyy H:mm:ss", $null)

Output:

PS C:\Users\Rob> $date = '13/02/2017 10:04:07'

[datetime]::ParseExact($date, "dd/MM/yyyy H:mm:ss", [cultureinfo]::InvariantCulture)
#or
[datetime]::ParseExact($date, "dd/MM/yyyy H:mm:ss", $null)

Monday, February 13, 2017 10:04:07 AM
Monday, February 13, 2017 10:04:07 AM