Construct AQS date range with PowerShell

4 Nov

For a script I needed to create an AQS (Advanced Query Syntax) Query that contained a date range.

An example of such is a range is: date:11/05/04..11/10/04

However we need to account for regional settings where for example the data seperator and the order of day and month may be different.

In my example I wanted to match any data that is 30 days or older so let’s do this in PowerShell:

First we define a variable for the minimum age:

# Minimum Age in Days
$MinAge = 30

1 2	# Minimum Age in Days $MinAge = 30

Then we use the MinValue property to get the a minimum date value (1/1/0001). This is the From date:

# DateFrom is MinValue to catch all
$DateFrom = [System.DateTime]::MinValue.ToShortDateString()

1 2	# DateFrom is MinValue to catch all $DateFrom = [System.DateTime]::MinValue.ToShortDateString()

And we use the ToShortDateString method to format the date in the current regional settings.

Now we can calculate the To Date:

# DateTo is the current date - $MinAge
$DateTo = [System.DateTime]::Now.Subtract([System.TimeSpan]::FromDays($MinAge)).ToShortDateString()

1 2	# DateTo is the current date - $MinAge $DateTo = [System.DateTime]::Now.Subtract([System.TimeSpan]::FromDays($MinAge)).ToShortDateString()

And the last step is to format the AQS Query String:

# Now Construct the AQL Query
$AQL = 'date:{0}..{1}' -f $DateFrom, $DateTo

1 2	# Now Construct the AQL Query $AQL = 'date:{0}..{1}' -f $DateFrom, $DateTo

In my case the result at the time of writing is:

date:1-1-0001..4-10-2011

1	date:1-1-0001..4-10-2011

Please consider donating something (even a small amount is ok) to support this site and my work:

Filed under: PowerShell

Remko Weijnen's Blog (Remko's Blog)