DateTime struggles!

Welcome Forums General PowerShell Q&A DateTime struggles!

Viewing 6 reply threads
  • Author
    Posts
    • #186529
      Participant
      Topics: 3
      Replies: 7
      Points: 37
      Rank: Member

      Hello all,

      Have been struggling with this for 2 days so hope somebody can stop me pulling out the rest of what little hair I have left out. I'm getting a date(string) from a CSV and then trying to convert it to a datetime object. While the conversion is easy enough it always swaps the day and month numbers around. I've done everything, including getting the culture as well as formatting as 'dd/MM/yyyy' however it always comes out as 'MM/dd/yyyy'. Yet when I do a (get-date -format "dd/MM/yyyy") its comes out as expected. It's just the string that has the problem.

      $CultureDateTimeFormat = (Get-Culture).DateTimeFormat
      $DateFormat = $CultureDateTimeFormat.ShortDatePattern
      $today = (get-date -format $DateFormat)
      
      import-csv C:\temp\terminations.csv -Header EmployeeID, Lastname, Firstname, TerminationDate | where { $_.TerminationDate -notlike "* TFR *" -and $_.EmployeeID } | sort Lastname, Firstname -Unique | foreach-object {
      
      $Lastname = $_.Lastname
      $firstname = $_.Firstname.split("")[0]
      $termstring = $_.TerminationDate
      $termdate = ($_.TerminationDate.split(":")[1].Trim()) -f (get-date)
      
      write-output "$termdate vs $today"
      
      The result will be something like: 10/27/2019 vs 05/11/2019 (using en-AU culture). Even though the string will be 27-OCT-2019.

       

       

    • #186538
      Participant
      Topics: 1
      Replies: 9
      Points: 84
      Helping Hand
      Rank: Member

      Hi,

      Is this "27-OCT-2019" the format how you are storing the terminationDate in the CSV?? it will be helpful if you can show the contents of the csv file.

    • #186544
      Participant
      Topics: 3
      Replies: 7
      Points: 37
      Rank: Member

      Hi,

      Is this "27-OCT-2019" the format how you are storing the terminationDate in the CSV?? it will be helpful if you can show the contents of the csv file.

      Yes, the dates are stored like this.

      It works if I get the date as a string but as soon as I convert to a datetime object the day and month swap around.

      • #186556
        Participant
        Topics: 1
        Replies: 9
        Points: 84
        Helping Hand
        Rank: Member

        As far as i understood, this is a formatting issue. I was able to repro the same issue. Using -f (get-date) did not help.

    • #186550
      Participant
      Topics: 1
      Replies: 9
      Points: 84
      Helping Hand
      Rank: Member

      Hi,

      Is this "27-OCT-2019" the format how you are storing the terminationDate in the CSV?? it will be helpful if you can show the contents of the csv file.

      Yes, the dates are stored like this.

      It works if I get the date as a string but as soon as I convert to a datetime object the day and month swap around.

      Okay. i hope if I understood correctly, this will help you.  In line 10 of your script,

      
      #get the date in string
      
      $termdate = ($_.TerminationDate.split(":")[1].Trim())
      
      #change it to datetime object as below
      
      $termdate = [datetime]::parseexact($termdate,'dd-MMM-yyyy',$null)
      
      #from here on you can play with the termdate object as you like
      
      #example: display the date in this format Oct/27/2019
      
      $termdate.ToString("MMM/dd/yyyy")
      
      #or display the date in this format 10/27/2019
      
      $termdate.ToString("MM/dd/yyyy")
      
      
      • #186709
        Participant
        Topics: 3
        Replies: 7
        Points: 37
        Rank: Member

        Thanks James, unfortunately this is still the undesired output to the below:

        Is 10/21/2019 00:00:00 before 6/11/2019?
        False

        To clarify, $termdate is imported from a CSV as "21-OCT-2019". Whenever I convert that to datetime it changes it to month first, then day. It needs to be day then month.

         

        $termdate = $_.TerminationDate.split(":")[1].Trim()
        $termdate = [datetime]::ParseExact($termdate,'dd-MMM-yyyy', $null)
        
        Write-Output "Is $($termdate) before $($today)?"
        $termdate -lt $today

         

         

      • #186727
        Participant
        Topics: 1
        Replies: 9
        Points: 84
        Helping Hand
        Rank: Member

        Thanks James, unfortunately this is still the undesired output to the below:

        Is 10/21/2019 00:00:00 before 6/11/2019?

        False

        To clarify, $termdate is imported from a CSV as "21-OCT-2019". Whenever I convert that to datetime it changes it to month first, then day. It needs to be day then month.

        PowerShell
        5 lines

        1
        2
        3
        4
        5
        $termdate = $_.TerminationDate.split(":")[1].Trim()
        $termdate = [datetime]::ParseExact($termdate,'dd-MMM-yyyy', $null)
        Write-Output "Is $($termdate) before $($today)?"
        $termdate -lt $today
        XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
        
        #The below statements return True for me.
        
        $termdate -lt (Get-Date) #or
        
        (Get-Date $termdate) -lt (Get-Date) #or
        
        $termdate.Date -lt (Get-Date)
        
        

        As i mentioned earlier, after ParseExact, it depends on how you play with the $termdate.

        As for the swapping of month and date, after the conversion, that's natural, i believe and you have to format it the way you want to use it.

    • #186619
      Participant
      Topics: 9
      Replies: 1277
      Points: 1,051
      Helping Hand
      Rank: Community Hero

      Try something like this:

      $termUsers = Import-CSV -Path C:\temp\terminations.csv -Header EmployeeID, Lastname, Firstname, TerminationDate | 
                   Select-Object -Property EmployeeID, Lastname, Firstname, @{Name='TerminationDate';Expression={[datetime]::ParseExact($_.TerminationDate,'dd-MMM-yyyy',$null)}}
                   Where-Object -FilterScript { $_.TerminationDate -notlike "* TFR *" -and $_.EmployeeID } | 
                   Sort-Object -Property Lastname, Firstname -Unique
      
      foreach ($user in $termUsers) {
      
      
          Write-Output '{0} vs {1}' -f (Get-Date $user.TerminationDate -Format "MM/dd/yyyy"), (Get-Date -Format "MM/dd/yyyy")
      }
      
      • #186712
        Participant
        Topics: 3
        Replies: 7
        Points: 37
        Rank: Member

        Thanks for the suggestion rob, but when I check the value of $user.Termination date it's still month/day.

        If a dates are strings can I compare them against one another or do they have to be datetime objects?

    • #186724
      Participant
      Topics: 6
      Replies: 658
      Points: 47
      Rank: Member

      Unless I'm miss-understanding, it seems it should be much simpler than what you are attempting.

      To be clear, the below is the same as what @james_yumnam provided earlier.

      $terminationDate = "27-OCT-2019"
      
      $termdate = [datetime]$terminationDate
      
      $today = get-date
      
      Write-Output "Is $($termdate.tostring('dd/MM/yyyy')) before $($today.tostring('dd/MM/yyyy'))?"
      
      $termdate.Date -lt $today.Date

      Results

      Is 27/10/2019 before 05/11/2019?
      True

       

      You can also have it use the culture as you have previously tried.  That way it will display in the configured culture of the system rather than hard-coding the format as above.  See example below.

      $terminationDate = "27-OCT-2019"
      
      $today = Get-Date
      $termdate = Get-Date $terminationDate
      
      $cultureShortDatePattern = (Get-culture).DateTimeFormat.ShortDatePattern
      
      Write-Output "Is $(Get-Date -Date $termdate -Format $cultureShortDatePattern) before $(Get-Date -Date $today -Format $cultureShortDatePattern)?"
      
      $termdate.Date -lt $today.Date

      Results for French culture

      Is 27/10/2019 before 05/11/2019?
      True

       

      • #186748
        Participant
        Topics: 3
        Replies: 7
        Points: 37
        Rank: Member

        Finally got it working with the below, tho still don't know what happened (that Date property perhaps). Hopefully never have to compare dates again!

        $Today = get-date
        
        $Lastname = $_.Lastname
        $Firstname = $_.Firstname.split("")[0]
        $TerminationDate = [datetime]$_.TerminationDate.split(":")[1].Trim()
        
        #MARK: Filter out future dates
        if ($TerminationDate.Date -ge $Today) { Write-Output "$TerminationDate is greater than $Today - skipping $Firstname $lastname" }
    • #186754
      Participant
      Topics: 1
      Replies: 9
      Points: 84
      Helping Hand
      Rank: Member

      Finally got it working with the below, tho still don't know what happened (that Date property perhaps). Hopefully never have to compare dates again!

      PowerShell
      8 lines

      1
      2
      3
      4
      5
      6
      7
      8
      $Today = get-date
      $Lastname = $_.Lastname
      $Firstname = $_.Firstname.split("")[0]
      $TerminationDate = [datetime]$_.TerminationDate.split(":")[1].Trim()
      #MARK: Filter out future dates
      if ($TerminationDate.Date -ge $Today) { Write-Output "$TerminationDate is greater than $Today – skipping $Firstname $lastname" }
      XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

      Great. It's the same as the 3rd option I have suggested to you. 🙂

Viewing 6 reply threads
  • You must be logged in to reply to this topic.