Convert French date to US

Welcome Forums General PowerShell Q&A Convert French date to US

This topic contains 7 replies, has 3 voices, and was last updated by

 
Participant
5 days, 21 hours ago.

  • Author
    Posts
  • #128379

    Participant
    Points: 21
    Rank: Member

    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

  • #128383

    Participant
    Points: 259
    Helping Hand
    Rank: Contributor

    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)"

  • #128389

    Participant
    Points: 21
    Rank: Member

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

  • #128398

    Participant
    Points: 21
    Rank: Member

    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

     

  • #128400

    Participant
    Points: 259
    Helping Hand
    Rank: Contributor

    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.

  • #128431

    Participant
    Points: 21
    Rank: Member

    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

  • #128434

    Participant
    Points: 21
    Rank: Member

    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

  • #128446

    Participant
    Points: 305
    Helping Hand
    Rank: Contributor

    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
    

You must be logged in to reply to this topic.