DateTime struggles!

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.

 

 

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.

[quote quote=186538]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.

[/quote]

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,

[pre]

#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”)

[/pre]

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.

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

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

 

 

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?

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

 

[quote quote=186709]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
<textarea class="ace_text-input" style="opacity: 0; height: 18px; width: 6.59781px; left: 44px; top: 0px;" spellcheck="false" wrap="off"></textarea>
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
[/quote]

[pre]

#The below statements return True for me.

$termdate -lt (Get-Date) #or

(Get-Date $termdate) -lt (Get-Date) #or

$termdate.Date -lt (Get-Date)

[/pre]

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.

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

[quote quote=186748]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
<textarea class="ace_text-input" style="opacity: 0; height: 18px; width: 6.59781px; left: 44px; top: 0px;" spellcheck="false" wrap="off"></textarea>
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
[/quote] Great. It's the same as the 3rd option I have suggested to you. :)