protected override string CompileBasicDateCondition(SqlResult context, BasicDateCondition condition)
{
string column = wrapper.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})";
}
string sql = $"{left} {condition.Operator} {Parameter(context, condition.Value)}";
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 = $"{column}::time";
}
else if (condition.Part == "date")
{
left = $"{column}::date";
}
else
{
left = $"DATE_PART('{condition.Part.ToUpperInvariant()}', {column})";
}
var sql = $"{left} {condition.Operator} {Parameter(ctx, condition.Value)}";
if (condition.IsNot)
{
return($"NOT ({sql})");
}
return(sql);
}
protected override string CompileBasicDateCondition(SqlResult ctx, BasicDateCondition condition)
{
var column = Wrap(condition.Column);
var part = condition.Part.ToUpper();
string left;
if (part == "TIME" || part == "DATE")
{
left = $"CAST({column} AS {part.ToUpper()})";
}
else
{
left = $"DATEPART({part.ToUpper()}, {column})";
}
var sql = $"{left} {condition.Operator} {Parameter(ctx, condition.Value)}";
if (condition.IsNot)
{
return($"NOT ({sql})");
}
return(sql);
}
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 context, BasicDateCondition condition)
{
string column = wrapper.Wrap(condition.Column);
string part = condition.Part.ToUpperInvariant();
string left;
if (part == "TIME" || part == "DATE")
{
left = $"CAST({column} AS {part.ToUpperInvariant()})";
}
else
{
left = $"DATEPART({part.ToUpperInvariant()}, {column})";
}
string sql = $"{left} {condition.Operator} {Parameter(context, condition.Value)}";
if (condition.IsNot)
{
sql = $"NOT ({sql})";
}
return(sql);
}
protected override string CompileBasicDateCondition(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 = $"DATEPART({condition.Part.ToUpper()}, {column})";
}
var sql = $"{left} {condition.Operator} {Parameter(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 virtual string CompileBasicDateCondition(BasicDateCondition x)
{
var column = Wrap(x.Column);
var sql = $"{x.Part.ToUpper()}({column}) {x.Operator} {Parameter(x.Value)}";
return(x.IsNot
? $"NOT ({sql})"
: sql);
}
protected override string CompileBasicDateCondition(SqlResult ctx, BasicDateCondition condition)
{
if (condition.Part != "time")
{
return(base.CompileBasicDateCondition(ctx, condition));
}
var column = Wrap(condition.Column);
string left;
string sql;
if (condition.Value is DateTime || condition.Value is TimeSpan)
{
const string format = @"hh\:mm\:ss\:ffffff";
string value;
if (condition.Value is DateTime)
{
value = ((DateTime)condition.Value).ToString(format);
}
else
{
value = ((TimeSpan)condition.Value).ToString(format);
}
condition.Value = value;
}
left = $"CAST({column} as TIME)";
sql = $"{left} {condition.Operator} CAST({Parameter(ctx, condition.Value)} as TIME)";
if (condition.IsNot)
{
return($"NOT ({sql})");
}
return(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);
}
