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); }