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.Quarter:
part = DateParts.Month;
number = builder.Mul(number, 3);
break;
case Sql.DateParts.DayOfYear:
case Sql.DateParts.WeekDay:
part = DateParts.Day;
break;
case Sql.DateParts.Week:
part = DateParts.Day;
number = builder.Mul(number, 7);
break;
}
var partSql = new SqlExpression(part.ToString());
builder.ResultExpression = new SqlFunction(typeof(DateTime?), "DateAdd", partSql, 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 function;
switch (part)
{
case Sql.DateParts.Year: function = "Add_Years"; break;
case Sql.DateParts.Quarter:
function = "Add_Months";
number = builder.Mul(number, 3);
break;
case Sql.DateParts.Month: function = "Add_Months"; break;
case Sql.DateParts.DayOfYear:
case Sql.DateParts.Day:
case Sql.DateParts.WeekDay: function = "Add_Days"; break;
case Sql.DateParts.Week:
function = "Add_Days";
number = builder.Mul(number, 7);
break;
case Sql.DateParts.Hour:
function = "Add_Seconds";
number = builder.Mul(number, 3600);
break;
case Sql.DateParts.Minute:
function = "Add_Seconds";
number = builder.Mul(number, 60);
break;
case Sql.DateParts.Second: function = "Add_Seconds"; break;
case Sql.DateParts.Millisecond:
function = "Add_Seconds";
number = builder.Div(number, 1000);
break;
default:
throw new ArgumentOutOfRangeException();
}
builder.ResultExpression = new SqlFunction(typeof(DateTime?), function, date, number);
}
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 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}) || ' Month'";
number = builder.Mul(number, 3);
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}) || ' Day'";
number = builder.Mul(number, 7);
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} || ' Millisecond'"; break;
default:
throw new ArgumentOutOfRangeException();
}
builder.ResultExpression = new SqlFunction(typeof(DateTime?), "DateTime", date,
new SqlExpression(expStr, Precedence.Additive, number));
}
public static ISqlExpression Mul <T>(this Sql.ISqExtensionBuilder builder, ISqlExpression left, ISqlExpression right)
{
return(builder.Mul(left, right, typeof(T)));
}
public static ISqlExpression Mul(this Sql.ISqExtensionBuilder builder, ISqlExpression expr1, int value)
{
return(builder.Mul <int>(expr1, new SqlValue(value)));
}
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 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();
}
}