public void Build(Sql.ISqExtensionBuilder builder)
{
string partStr;
var part = builder.GetValue <Sql.DateParts>("part");
switch (part)
{
case Sql.DateParts.Year: partStr = "To_Number(To_Char({date}, 'YYYY'))"; break;
case Sql.DateParts.Quarter: partStr = "To_Number(To_Char({date}, 'Q'))"; break;
case Sql.DateParts.Month: partStr = "To_Number(To_Char({date}, 'MM'))"; break;
case Sql.DateParts.DayOfYear: partStr = "To_Number(To_Char({date}, 'DDD'))"; break;
case Sql.DateParts.Day: partStr = "To_Number(To_Char({date}, 'DD'))"; break;
case Sql.DateParts.Week: partStr = "To_Number(To_Char({date}, 'WW'))"; break;
case Sql.DateParts.WeekDay:
{
builder.Expression = "Mod(1 + Trunc({date}) - Trunc({date}, 'IW'), 7)";
builder.ResultExpression = builder.Inc(builder.ConvertToSqlExpression(Precedence.Primary));
return;
}
case Sql.DateParts.Hour: partStr = "To_Number(To_Char({date}, 'HH24'))"; break;
case Sql.DateParts.Minute: partStr = "To_Number(To_Char({date}, 'MI'))"; break;
case Sql.DateParts.Second: partStr = "To_Number(To_Char({date}, 'SS'))"; break;
case Sql.DateParts.Millisecond: partStr = "To_Number(To_Char({date}, 'FF'))"; break;
default:
throw new ArgumentOutOfRangeException();
}
builder.Expression = partStr;
}
public void Build(Sql.ISqExtensionBuilder builder)
{
var part = builder.GetValue <Sql.DateParts>("part");
var date = builder.GetExpression("date");
var number = builder.GetExpression("number");
string expStr;
switch (part)
{
case Sql.DateParts.Year: expStr = "{0} * Interval '1 Year'"; break;
case Sql.DateParts.Quarter: expStr = "{0} * Interval '1 Month' * 3"; break;
case Sql.DateParts.Month: expStr = "{0} * Interval '1 Month'"; break;
case Sql.DateParts.DayOfYear:
case Sql.DateParts.WeekDay:
case Sql.DateParts.Day: expStr = "{0} * Interval '1 Day'"; break;
case Sql.DateParts.Week: expStr = "{0} * Interval '1 Day' * 7"; break;
case Sql.DateParts.Hour: expStr = "{0} * Interval '1 Hour'"; break;
case Sql.DateParts.Minute: expStr = "{0} * Interval '1 Minute'"; break;
case Sql.DateParts.Second: expStr = "{0} * Interval '1 Second'"; break;
case Sql.DateParts.Millisecond: expStr = "{0} * Interval '1 Millisecond'"; break;
default:
throw new ArgumentOutOfRangeException();
}
builder.ResultExpression = builder.Add(
date,
new SqlExpression(typeof(TimeSpan?), expStr, Precedence.Multiplicative, number),
typeof(DateTimeOffset?));
}
public void Build(Sql.ISqExtensionBuilder builder)
{
var part = builder.GetValue <Sql.DateParts>("part");
var date = builder.GetExpression("date");
var number = builder.GetExpression("number");
string expStr;
switch (part)
{
case Sql.DateParts.Year: expStr = "{0} Year"; break;
case Sql.DateParts.Quarter: expStr = "({0} * 3) Month"; break;
case Sql.DateParts.Month: expStr = "{0} Month"; break;
case Sql.DateParts.DayOfYear:
case Sql.DateParts.WeekDay:
case Sql.DateParts.Day: expStr = "{0} Day"; break;
case Sql.DateParts.Week: expStr = "({0} * 7) Day"; break;
case Sql.DateParts.Hour: expStr = "{0} Hour"; break;
case Sql.DateParts.Minute: expStr = "{0} Minute"; break;
case Sql.DateParts.Second: expStr = "{0} Second"; break;
case Sql.DateParts.Millisecond: expStr = "({0} / 1000.0) Second"; break;
default:
throw new ArgumentOutOfRangeException();
}
builder.ResultExpression = builder.Add(
date,
new SqlExpression(typeof(TimeSpan?), expStr, Precedence.Primary, number),
typeof(DateTime?));
}
public void Build(Sql.ISqExtensionBuilder builder)
{
var part = builder.GetValue <Sql.DateParts>("part");
var date = builder.GetExpression("date");
var number = builder.GetExpression("number");
string expStr;
switch (part)
{
case Sql.DateParts.Year: expStr = "{0} * INTERVAL '1' YEAR"; break;
case Sql.DateParts.Quarter: expStr = "{0} * INTERVAL '3' MONTH"; break;
case Sql.DateParts.Month: expStr = "{0} * INTERVAL '1' MONTH"; break;
case Sql.DateParts.DayOfYear:
case Sql.DateParts.WeekDay:
case Sql.DateParts.Day: expStr = "{0} * INTERVAL '1' DAY"; break;
case Sql.DateParts.Week: expStr = "{0} * INTERVAL '7' DAY"; break;
case Sql.DateParts.Hour: expStr = "{0} * INTERVAL '1' HOUR"; break;
case Sql.DateParts.Minute: expStr = "{0} * INTERVAL '1' MINUTE"; break;
case Sql.DateParts.Second: expStr = "{0} * INTERVAL '1' SECOND"; break;
case Sql.DateParts.Millisecond: expStr = "{0} * INTERVAL '0.001' SECOND"; break;
default:
throw new ArgumentOutOfRangeException();
}
builder.ResultExpression = builder.Add(
date,
new SqlExpression(typeof(TimeSpan?), expStr, Precedence.Multiplicative, number),
typeof(DateTime?));
}
public void Build(Sql.ISqExtensionBuilder builder)
{
string exprStr;
var part = builder.GetValue <Sql.DateParts>("part");
switch (part)
{
case Sql.DateParts.Year: exprStr = "Year({date})"; break;
case Sql.DateParts.Quarter:
builder.Expression = "Floor((Month({date})-1) / 3)";
builder.ResultExpression = builder.Inc(builder.ConvertToSqlExpression());
return;
case Sql.DateParts.Month: exprStr = "Month({date})"; break;
case Sql.DateParts.DayOfYear: exprStr = "DayOfYear({date})"; break;
case Sql.DateParts.Day: exprStr = "DayOfMonth({date})"; break;
case Sql.DateParts.Week: exprStr = "Week({date})"; break;
case Sql.DateParts.WeekDay:
builder.Expression = "MOD(Weekday({date}) + 1, 7)";
builder.ResultExpression = builder.Inc(builder.ConvertToSqlExpression());
return;
case Sql.DateParts.Hour: exprStr = "Hour({date})"; break;
case Sql.DateParts.Minute: exprStr = "Minute({date})"; break;
case Sql.DateParts.Second: exprStr = "Second({date})"; break;
default:
throw new ArgumentOutOfRangeException();
}
builder.Expression = exprStr;
}
public void Build(Sql.ISqExtensionBuilder builder)
{
var part = builder.GetValue <Sql.DateParts>("part");
var date = builder.GetExpression("date");
var number = builder.GetExpression("number");
switch (part)
{
case Sql.DateParts.Year:
builder.ResultExpression = new SqlFunction(typeof(DateTime?), "Add_Months", date, builder.Mul(number, 12));
break;
case Sql.DateParts.Quarter:
builder.ResultExpression = new SqlFunction(typeof(DateTime?), "Add_Months", date, builder.Mul(number, 3));
break;
case Sql.DateParts.Month:
builder.ResultExpression = new SqlFunction(typeof(DateTime?), "Add_Months", builder.GetExpression("date"), builder.GetExpression("number"));
break;
case Sql.DateParts.DayOfYear:
case Sql.DateParts.WeekDay:
case Sql.DateParts.Day: builder.ResultExpression = builder.Add <DateTime>(date, number); break;
case Sql.DateParts.Week: builder.ResultExpression = builder.Add <DateTime>(date, builder.Mul(number, 7)); break;
case Sql.DateParts.Hour: builder.ResultExpression = builder.Add <DateTime>(date, builder.Div(number, 24)); break;
case Sql.DateParts.Minute: builder.ResultExpression = builder.Add <DateTime>(date, builder.Div(number, 60 * 24)); break;
case Sql.DateParts.Second: builder.ResultExpression = builder.Add <DateTime>(date, builder.Div(number, 60 * 60 * 24)); break;
// adding number to timestamp instead of adding interval leads to wrong result type and loose of precision
case Sql.DateParts.Millisecond: builder.ResultExpression = builder.Add <DateTime>(date, builder.Mul(new SqlExpression("interval '0.001' second", Precedence.Primary), number, typeof(int))); break;
default:
throw new ArgumentOutOfRangeException();
}
}
public void Build(Sql.ISqExtensionBuilder builder)
{
var expStr = builder.GetValue <Sql.DateParts>("part") switch {
Sql.DateParts.Year => "{0} + {1} Year",
Sql.DateParts.Quarter => "{0} + ({1} * 3) Month",
Sql.DateParts.Month => "{0} + {1} Month",
Sql.DateParts.DayOfYear => "{0} + {1} Day",
Sql.DateParts.Day => "{0} + {1} Day",
Sql.DateParts.WeekDay => "{0} + {1} Day",
Sql.DateParts.Week => "{0} + ({1} * 7) Day",
Sql.DateParts.Hour => "{0} + {1} Hour",
Sql.DateParts.Minute => "{0} + {1} Minute",
Sql.DateParts.Second => "{0} + {1} Second",
Sql.DateParts.Millisecond => "{0} + ({1} * 1000) Microsecond",
_ => throw new ArgumentOutOfRangeException()
};
var date = builder.GetExpression("date");
var number = builder.GetExpression("number");
builder.ResultExpression = new SqlExpression(typeof(DateTime?), expStr, 60, date, number);
}
}
public void Build(Sql.ISqExtensionBuilder builder)
{
var part = builder.GetValue <Sql.DateParts>("part");
var date = builder.GetExpression("date");
var number = builder.GetExpression("number");
switch (part)
{
case Sql.DateParts.Year:
builder.ResultExpression = new SqlFunction(typeof(DateTime?), "Add_Months", date, builder.Mul(number, 12));
break;
case Sql.DateParts.Quarter:
builder.ResultExpression = new SqlFunction(typeof(DateTime?), "Add_Months", date, builder.Mul(number, 3));
break;
case Sql.DateParts.Month:
builder.ResultExpression = new SqlFunction(typeof(DateTime?), "Add_Months", builder.GetExpression("date"), builder.GetExpression("number"));
break;
case Sql.DateParts.DayOfYear:
case Sql.DateParts.WeekDay:
case Sql.DateParts.Day: builder.ResultExpression = builder.Add <DateTime>(date, number); break;
case Sql.DateParts.Week: builder.ResultExpression = builder.Add <DateTime>(date, builder.Mul(number, 7)); break;
case Sql.DateParts.Hour: builder.ResultExpression = builder.Add <DateTime>(date, builder.Div(number, 24)); break;
case Sql.DateParts.Minute: builder.ResultExpression = builder.Add <DateTime>(date, builder.Div(number, 60 * 24)); break;
case Sql.DateParts.Second: builder.ResultExpression = builder.Add <DateTime>(date, builder.Div(number, 60 * 60 * 24)); break;
case Sql.DateParts.Millisecond: builder.ResultExpression = builder.Add <DateTime>(date, builder.Div(number, 1000 * 60 * 60 * 24)); break;
default:
throw new ArgumentOutOfRangeException();
}
}
public void Build(Sql.ISqExtensionBuilder builder)
{
var part = builder.GetValue <Sql.DateParts>("part");
var date = builder.GetExpression("date");
var number = builder.GetExpression("number");
var partStr = part switch
{
Sql.DateParts.Year => "yyyy",
Sql.DateParts.Quarter => "q",
Sql.DateParts.Month => "m",
Sql.DateParts.DayOfYear => "y",
Sql.DateParts.Day => "d",
Sql.DateParts.Week => "ww",
Sql.DateParts.WeekDay => "w",
Sql.DateParts.Hour => "h",
Sql.DateParts.Minute => "n",
Sql.DateParts.Second => "s",
_ => throw new ArgumentOutOfRangeException(),
};
builder.ResultExpression = new SqlFunction(typeof(DateTime?), "DateAdd",
new SqlValue(partStr), number, date);
}
public void Build(Sql.ISqExtensionBuilder builder)
{
var modifier = builder.GetValue <MatchModifier>("modifier");
switch (modifier)
{
case MatchModifier.NaturalLanguage:
// default modifier, no need to add it to SQL
break;
case MatchModifier.Boolean:
builder.AddExpression("modifier", " IN BOOLEAN MODE");
break;
case MatchModifier.WithQueryExpansion:
// use short form without 'IN NATURAL LANGUAGE MODE' prefix
builder.AddExpression("modifier", " WITH QUERY EXPANSION");
break;
default:
throw new ArgumentOutOfRangeException("modifier");
}
}
public void Build(Sql.ISqExtensionBuilder builder)
{
var part = builder.GetValue <Sql.DateParts>("part");
var date = builder.GetExpression("date");
var number = builder.GetExpression("number");
string partStr;
switch (part)
{
case Sql.DateParts.Year: partStr = "yyyy"; break;
case Sql.DateParts.Quarter: partStr = "q"; break;
case Sql.DateParts.Month: partStr = "m"; break;
case Sql.DateParts.DayOfYear: partStr = "y"; break;
case Sql.DateParts.Day: partStr = "d"; break;
case Sql.DateParts.Week: partStr = "ww"; break;
case Sql.DateParts.WeekDay: partStr = "w"; break;
case Sql.DateParts.Hour: partStr = "h"; break;
case Sql.DateParts.Minute: partStr = "n"; break;
case Sql.DateParts.Second: partStr = "s"; break;
default:
throw new ArgumentOutOfRangeException();
}
builder.ResultExpression = new SqlFunction(typeof(DateTime?), "DateAdd",
new SqlValue(partStr), number, date);
}
public void Build(Sql.ISqExtensionBuilder builder)
{
var part = builder.GetValue <Sql.DateParts>("part");
var date = builder.GetExpression("date");
var number = builder.GetExpression("number");
string expStr;
switch (part)
{
case Sql.DateParts.Year: expStr = "{0} + ({1}) Year"; break;
case Sql.DateParts.Quarter: expStr = "{0} + (({1}) * 3) Month"; break;
case Sql.DateParts.Month: expStr = "{0} + ({1}) Month"; break;
case Sql.DateParts.DayOfYear:
case Sql.DateParts.WeekDay:
case Sql.DateParts.Day: expStr = "{0} + ({1}) Day"; break;
case Sql.DateParts.Week: expStr = "{0} + (({1}) * 7) Day"; break;
case Sql.DateParts.Hour: expStr = "{0} + ({1}) Hour"; break;
case Sql.DateParts.Minute: expStr = "{0} + ({1}) Minute"; break;
case Sql.DateParts.Second: expStr = "{0} + ({1}) Second"; break;
case Sql.DateParts.Millisecond: expStr = "{0} + (({1}) * 1000) Microsecond"; break;
default:
throw new ArgumentOutOfRangeException();
}
builder.ResultExpression = new SqlExpression(typeof(DateTime?), expStr, Precedence.Additive, date, number);
}
public void Build(Sql.ISqExtensionBuilder builder)
{
var part = builder.GetValue <Sql.DateParts>("part");
var date = builder.GetExpression("date");
var number = builder.GetExpression("number");
string expStr = "strftime('%Y-%m-%d %H:%M:%f', {0},";
switch (part)
{
case Sql.DateParts.Year: expStr += "{1} || ' Year')"; break;
case Sql.DateParts.Quarter: expStr += "({1}*3) || ' Month')"; break;
case Sql.DateParts.Month: expStr += "{1} || ' Month')"; break;
case Sql.DateParts.DayOfYear:
case Sql.DateParts.WeekDay:
case Sql.DateParts.Day: expStr += "{1} || ' Day')"; break;
case Sql.DateParts.Week: expStr += "({1}*7) || ' Day')"; break;
case Sql.DateParts.Hour: expStr += "{1} || ' Hour')"; break;
case Sql.DateParts.Minute: expStr += "{1} || ' Minute')"; break;
case Sql.DateParts.Second: expStr += "{1} || ' Second')"; break;
case Sql.DateParts.Millisecond: expStr += "({1}/1000.0) || ' Second')"; break;
default:
throw new ArgumentOutOfRangeException();
}
builder.ResultExpression = new SqlExpression(typeof(DateTime?), expStr, Precedence.Concatenate, date, number);
}
public void Build(Sql.ISqExtensionBuilder builder)
{
builder.AddExpression("funcName", builder.GetValue <string>("funcName"));
builder.AddExpression("fieldName", builder.GetValue <string>("fieldName"));
}
public void Build(Sql.ISqExtensionBuilder builder)
{
string exprStr;
var part = builder.GetValue <Sql.DateParts>("part");
switch (part)
{
case Sql.DateParts.Year: exprStr = "Year({date})"; break;
case Sql.DateParts.Quarter:
{
builder.Expression = "Month({date})";
builder.ResultExpression =
builder.Inc(builder.Div(builder.Dec(builder.ConvertToSqlExpression(Precedence.Primary)), 3));
return;
}
case Sql.DateParts.Month: exprStr = "Month({date})"; break;
case Sql.DateParts.DayOfYear:
{
var param = builder.GetExpression("date");
builder.ResultExpression = builder.Inc(
builder.Sub <int>(
new SqlFunction(typeof(DateTime?), "Mdy",
new SqlFunction(typeof(int?), "Month", param),
new SqlFunction(typeof(int?), "Day", param),
new SqlFunction(typeof(int?), "Year", param)),
new SqlFunction(typeof(DateTime?), "Mdy",
new SqlValue(1),
new SqlValue(1),
new SqlFunction(typeof(int?), "Year", param)))
);
return;
}
case Sql.DateParts.Day: exprStr = "Day({date})"; break;
case Sql.DateParts.Week: exprStr = "((Extend({date}, year to day) - (Mdy(12, 31 - WeekDay(Mdy(1, 1, year({date}))), Year({date}) - 1) + Interval(1) day to day)) / 7 + Interval(1) day to day)::char(10)::int"; break;
case Sql.DateParts.WeekDay:
{
builder.Expression = "weekDay({date})";
builder.ResultExpression = builder.Inc(builder.ConvertToSqlExpression(Precedence.Primary));
return;
}
case Sql.DateParts.Hour: exprStr = "({date}::datetime Hour to Hour)::char(3)::int"; break;
case Sql.DateParts.Minute: exprStr = "({date}::datetime Minute to Minute)::char(3)::int"; break;
case Sql.DateParts.Second: exprStr = "({date}::datetime Second to Second)::char(3)::int"; break;
case Sql.DateParts.Millisecond: exprStr = "Millisecond({date})"; break;
default:
throw new ArgumentOutOfRangeException();
}
builder.Expression = exprStr;
}