Powershell is not recognising a date that has been formatted in datetime format

Question

I am searching Splunk using powershell via API and get a list of data that contain dates (not in DateTime format).

The API call is performed by a Function that works fine and it works for the inital search which required the search and a start and end date/time.

Once the initial search completes I am looping over the results and performing sub-searches looking (in Splunk) looking for additional data from a different index.

I take the returned date and time (e.g. 16/02/2024 12:45:27) split on the space and just return the date portion and then create two fields:

$EmailSearchStartdt = "$(($DateConversion).split(" ")[0]) 00:00:00" 
$EmailSearchEnddt = "$(($DateConversion).split(" ")[0]) 23:59:59" 

This is all failing on the the two date fields. I'm taking the dates from the initial search and turning them back into datetime as so:

$EmailSearchStartdt = ([datetime]::ParseExact($EmailSearchStartdt,"dd/MM/yyyy HH:mm:ss", [System.Globalization.CultureInfo]::GetCultureInfo("en-GB")))

$EmailSearchEnddt = ([datetime]::ParseExact($EmailSearchEnddt,"dd/MM/yyyy HH:mm:ss", [System.Globalization.CultureInfo]::GetCultureInfo("en-GB")))

The problem is Powershell is reversing the day and month into US format (I'm in the UK with UK timezone settings etc) and when fed back into the function I get the following:

EmailSearchStartdt: 15/03/2024 00:00:00 (this is the string)
EmailSearchStartdt: **03/15**/2024 00:00:00 (this is after the parseexact)
EmailSearchEnddt: 15/03/2024 23:59:59 (this is the string)
EmailSearchEnddt: **03/15**/2024 23:59:59 (this is after the parseexact)

The result is Splunk finds nothing as the date has been reversed.

If I do the following:

$EmailSearchStartdt = get-date -date $EmailSearchStartdt -Format "dd MMM yyyy HH:mm:ss"
$EmailSearchEnddt = get-date -date $EmailSearchEnddt -Format "dd MMM yyyy HH:mm:ss"

Splunk seems to accept this but if I try:

$EmailSearchStartdt = (get-date -date $EmailSearchStartdt -Format "dd MMM yyyy HH:mm:ss").adddays(-3)

$EmailSearchEnddt = (get-date -date $EmailSearchEnddt -Format "dd MMM yyyy HH:mm:ss").adddays(1)

I get: Method invocation failed because [System.String] does not contain a method named 'adddays'.

So Powershell is seeing this as a string.

Ultimately the script is being invked with two dates that are strings but the function rejects this when its called internally with the sub-searches.

This has been literally driving me utterly mad so any help would be appreciated!

Answer 1

The core problem is that expandable (interpolating), double-quoted strings ("...") in PowerShell use the invariant culture for stringifying non-string values - irrespective of what culture is currently effect (as reported by Get-Culture), and the invariant culture is based on (but distinct from) the en-US culture with its month-first date format.

For instance, an expandable string such as "$(Get-Date 1970-12-01)" always yields 12/01/1970 00:00:00, i.e. it reports the month first, which explains the results of your own attempts (e.g, "$(($DateConversion).split(" ")[0]) 00:00:00").

By contrast, the [datetime] type's .ToString() method (its parameter-less overload) uses the current culture, so that (Get-Date 1970-12-01).ToString()'s output varies by culture, and yields 01/12/1970 00:00:00 (day first) in the en-GB culture - however you may pass a cultural context explicitly, as shown below.

Therefore, it's best to perform any calculations and modifications based on [datetime] instances first, and then create a string representation explicitly based on the culture of interest (which may be the current one).

In your case, this means:

# Parse the input timestamp based on the given culture into
# [datetime] instances.
$culture = [cultureinfo] 'en-GB'
$format = 'dd/MM/yyyy HH:mm:ss'
$EmailSearchStartdt = [datetime]::ParseExact($EmailSearchStartdt, $format, $culture)
$EmailSearchEnddt = [datetime]::ParseExact($EmailSearchEnddt, $format, $culture)

# Modify these timestamps to refer to the start and end of the
# calendar day (.Date returns the start of the calendar day, 00:00:00)
$EmailSearchStartdt = $EmailSearchStartdt.Date
$EmailSearchEnddt = $EmailSearchStartdt.Date.AddDays(1).AddTicks(-1)

# Now stringify them as needed.
$outFormat = 'dd MMM yyyy HH:mm:ss'
$EmailSearchStartdt.ToString($outFormat, $culture)
$EmailSearchEnddt.ToString($outFormat, $culture)

Note that if your current culture is also the target culture, you can omit the $culture argument in the calls above.

Answer 2

The problem is the initial $(($DateConversion)) interpolation is not creating the result you expect. Everything else proceeds from there.

An important thing to understand here is Powershell (and .Net) have an internal DateTime type that is not built using strings at all. Because of the initial formatting issue, I suspect the $DateConversion variable already uses this DateTime type to store the correct value, and you're doing a lot of extra work for nothing.

But before I come to the solution, there are a few things to note:

For the get-date cmdlet, keep in mind the -Format argument is only ever about output formats — it causes get-date to create a string instead of a real DateTime object — and never about the input format. This is why AddDays() is not available: you have a string instead of a DateTime. The -date argument also only uses the system (not invariant) locale. If you need a different locale, use something from the .Net DateTime::Parse() family of methods.

But really, the correct way to handle a range for a single day is NOT to check from 00:00:00 to 11:59:59 of that day. Rather, you should use a half-open range with an exclusive upper boundary for the first instant (00:00:00) of the next day. Furthermore, the correct way to truncate a value back to the beginning (or end) of the day is NOT to output to a string and then reparse with 00:00:00 (or 11:59:59) as the time value.

As you are discovering, thanks to cultural/internationalization issues, converting between strings and dates is always a much more complicated process than we expect, which makes it slower and more error-prone than we'd like. Instead, avoid using string manipulation to edit dates; keep the values as DateTime objects for as long as possible, and use the methods and properties provided by the type... for example, the .Date property.

Put it all together and you should be able to reduce this all down to this:

$EmailSearchStartdt = $DateConversion.Date
$EmailSearchEnddt = $DateConversion.Date.AddDays(1)

Keeping in mind the search itself should now also be changed to use an exclusive upper boundary (</-lt instead of <=/-le). If this change is not possible, you can still likely reduce it to this:

$EmailSearchStartdt = $DateConversion.Date
$EmailSearchEnddt = $DateConversion.Date.AddDays(1).AddTicks(-1)

Which will also protect you against missing messages that might be sent during the last second of the day.

If you still need these values as strings to send to Splunk, you can use .ToString() with whatever cultural option you want. But again: I said, "If", and you should avoid this if you can. It's better to keep these things as DateTime objects for as long as you possibly can.

And now we have code that is much shorter, more correct, less susceptible to odd cultural issues, and (as a bonus) faster.

Finally, there is possibility I'm wrong about the $DateConversion data type. If this is true, the best option is still to parse to a DateTime object initially, which you can even re-assign back to this same variable. And then you can still use the code I recommended to create your final date range.

The important thing is to train yourself to stop thinking about date variables in terms of strings.