Skip to content

Instantly share code, notes, and snippets.

@skalahonza
Created June 16, 2020 12:56
Show Gist options
  • Save skalahonza/3a2e8c497c9b3a5e72a8f8803e3da6f6 to your computer and use it in GitHub Desktop.
Save skalahonza/3a2e8c497c9b3a5e72a8f8803e3da6f6 to your computer and use it in GitHub Desktop.
Construct OData date time query that compares date time part by part. Suitable for Logic Apps SQL server with Data Gateway
private static string GreaterOrEqual(string first, string second, string part) =>
$"{part}({first}) ge {part}({second})";
private static string GreaterThan(string first, string second, string part) =>
$"{part}({first}) gt {part}({second})";
private static string Equal(string first, string second, string part) =>
$"{part}({first}) eq {part}({second})";
/// <summary>
/// Construct OData query for date time comparison
/// </summary>
/// <param name="first">First operand</param>
/// <param name="second">Second operand</param>
/// <param name="parts">Sorted date time parts to compare</param>
/// <example>
/// <code>
/// OdataDateTime("change", "@{variables('Timestamp')}", "year", "month", "day", "hour", "minute")
/// </code>
/// </example>
/// <returns></returns>
private static string OdataDateTime(string first, string second, params string[] parts)
{
if (parts.Length == 0) throw new ArgumentException("Array cannot be empty.", nameof(parts));
//minute(change) ge minute(@{variables('Timestamp')})
else if (parts.Length == 1) return GreaterOrEqual(first,second, parts[0]);
//minute(change) gt minute(@{variables('Timestamp')}) or (first part is equal AND all other parts are greater or equal)
else return $"{GreaterThan(first, second, parts[0])} or (({Equal(first, second, parts[0])}) and ({OdataDateTime(first, second, parts[1..])}))";
}
static void Main(string[] args)
{
var query = OdataDateTime("change", "@{variables('Timestamp')}", "year", "month", "day", "hour", "minute");
Console.WriteLine($"({query}) and deleted ne true");
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment