public void Build(Sql.ISqExtensionBuilder builder)
{
var startDate = builder.GetExpression(1);
var endDate = builder.GetExpression(2);
var secondsExpr = builder.Mul <int>(builder.Sub <int>(new SqlFunction(typeof(int), "Days", endDate), new SqlFunction(typeof(int), "Days", startDate)), new SqlValue(86400));
var midnight = builder.Sub <int>(new SqlFunction(typeof(int), "MIDNIGHT_SECONDS", endDate), new SqlFunction(typeof(int), "MIDNIGHT_SECONDS", startDate));
var resultExpr = builder.Add <int>(secondsExpr, midnight);
resultExpr = builder.GetValue <Sql.DateParts>(0) switch {
Sql.DateParts.Day => builder.Div(resultExpr, 86400),
Sql.DateParts.Hour => builder.Div(resultExpr, 3600),
Sql.DateParts.Minute => builder.Div(resultExpr, 60),
Sql.DateParts.Millisecond => builder.Add <int>(builder.Mul(resultExpr, 1000), builder.Div(builder.Sub <int>(new SqlFunction(typeof(int), "MICROSECOND", endDate), new SqlFunction(typeof(int), "MICROSECOND", startDate)), 1000)),
_ => throw new ArgumentOutOfRangeException()
};
builder.ResultExpression = resultExpr;
}
}
public void Build(Sql.ISqExtensionBuilder builder)
{
var part = builder.GetValue <Sql.DateParts>(0);
var startDate = builder.GetExpression(1);
var endDate = builder.GetExpression(2);
var secondsExpr = builder.Mul <int>(builder.Sub <int>(
new SqlFunction(typeof(int), "Days", endDate),
new SqlFunction(typeof(int), "Days", startDate)),
new SqlValue(86400));
var midnight = builder.Sub <int>(
new SqlFunction(typeof(int), "MIDNIGHT_SECONDS", endDate),
new SqlFunction(typeof(int), "MIDNIGHT_SECONDS", startDate));
var resultExpr = builder.Add <int>(secondsExpr, midnight);
switch (part)
{
case Sql.DateParts.Day: resultExpr = builder.Div(resultExpr, 86400); break;
case Sql.DateParts.Hour: resultExpr = builder.Div(resultExpr, 3600); break;
case Sql.DateParts.Minute: resultExpr = builder.Div(resultExpr, 60); break;
case Sql.DateParts.Second: break;
case Sql.DateParts.Millisecond:
resultExpr = builder.Add <int>(
builder.Mul(resultExpr, 1000),
builder.Div(
builder.Sub <int>(
new SqlFunction(typeof(int), "MICROSECOND", endDate),
new SqlFunction(typeof(int), "MICROSECOND", startDate)),
1000));
break;
default:
throw new ArgumentOutOfRangeException();
}
builder.ResultExpression = resultExpr;
}
public static ISqlExpression Dec(this Sql.ISqExtensionBuilder builder, ISqlExpression expr)
{
return(builder.Sub(expr, 1));
}
public static ISqlExpression Sub(this Sql.ISqExtensionBuilder builder, ISqlExpression left, int value)
{
return(builder.Sub <int>(left, new SqlValue(value)));
}
public static ISqlExpression Sub <T>(this Sql.ISqExtensionBuilder builder, ISqlExpression left, ISqlExpression right)
{
return(builder.Sub(left, right, typeof(T)));
}
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;
}