protected override string CompileBasicDateCondition(SqlResult ctx, BasicDateCondition condition)
{
var column = Wrap(condition.Column);
var value = Parameter(ctx, condition.Value);
var formatMap = new Dictionary <string, string> {
{ "date", "%Y-%m-%d" },
{ "time", "%H:%M:%S" },
{ "year", "%Y" },
{ "month", "%m" },
{ "day", "%d" },
{ "hour", "%H" },
{ "minute", "%M" },
};
if (!formatMap.ContainsKey(condition.Part))
{
return($"{column} {condition.Operator} {value}");
}
var sql = $"strftime('{formatMap[condition.Part]}', {column}) {condition.Operator} cast({value} as text)";
if (condition.IsNot)
{
return($"NOT ({sql})");
}
return(sql);
}
protected override string CompileBasicDateCondition(SqlResult ctx, BasicDateCondition condition)
{
var column = Wrap(condition.Column);
string left;
if (condition.Part == "time")
{
left = $"CAST({column} as TIME)";
}
else if (condition.Part == "date")
{
left = $"CAST({column} as DATE)";
}
else
{
left = $"EXTRACT({condition.Part.ToUpperInvariant()} FROM {column})";
}
var sql = $"{left} {condition.Operator} {Parameter(ctx, condition.Value)}";
if (condition.IsNot)
{
return($"NOT ({sql})");
}
return(sql);
}
protected virtual string CompileBasicDateCondition(SqlResult ctx, BasicDateCondition x)
{
var column = Wrap(x.Column);
var op = checkOperator(x.Operator);
var sql = $"{x.Part.ToUpperInvariant()}({column}) {op} {Parameter(ctx, x.Value)}";
return(x.IsNot ? $"NOT ({sql})" : sql);
}
protected override string CompileBasicDateCondition(SqlResult ctx, BasicDateCondition condition)
{
var column = Wrap(condition.Column);
var value = Parameter(ctx, condition.Value);
var sql = "";
var valueFormat = "";
var isDateTime = (condition.Value is DateTime dt);
switch (condition.Part)
{
case "date": // assume YY-MM-DD format
if (isDateTime)
{
valueFormat = $"{value}";
}
else
{
valueFormat = $"TO_DATE({value}, 'YY-MM-DD')";
}
sql = $"TO_CHAR({column}, 'YY-MM-DD') {condition.Operator} TO_CHAR({valueFormat}, 'YY-MM-DD')";
break;
case "time":
if (isDateTime)
{
valueFormat = $"{value}";
}
else
{
// assume HH:MM format
if (condition.Value.ToString().Split(':').Count() == 2)
{
valueFormat = $"TO_DATE({value}, 'HH24:MI')";
}
else // assume HH:MM:SS format
{
valueFormat = $"TO_DATE({value}, 'HH24:MI:SS')";
}
}
sql = $"TO_CHAR({column}, 'HH24:MI:SS') {condition.Operator} TO_CHAR({valueFormat}, 'HH24:MI:SS')";
break;
case "year":
case "month":
case "day":
case "hour":
case "minute":
case "second":
sql = $"EXTRACT({condition.Part.ToUpperInvariant()} FROM {column}) {condition.Operator} {value}";
break;
default:
sql = $"{column} {condition.Operator} {value}";
break;
}
if (condition.IsNot)
{
return($"NOT ({sql})");
}
return(sql);
}
