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 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) { string?partStr = null; var part = builder.GetValue <Sql.DateParts>("part"); switch (part) { case Sql.DateParts.Year: partStr = "Y"; break; case Sql.DateParts.Quarter: builder.Expression = "Cast(strFTime('%m', {date}) as int)"; builder.ResultExpression = builder.Inc(builder.Div(builder.Dec(builder.ConvertToSqlExpression(Precedence.Primary)), 3)); break; case Sql.DateParts.Month: partStr = "m"; break; case Sql.DateParts.DayOfYear: partStr = "j"; break; case Sql.DateParts.Day: partStr = "d"; break; case Sql.DateParts.Week: partStr = "W"; break; case Sql.DateParts.WeekDay: builder.Expression = "Cast(strFTime('%w', {date}) as int)"; builder.ResultExpression = builder.Inc(builder.ConvertToSqlExpression(Precedence.Primary)); break; case Sql.DateParts.Hour: partStr = "H"; break; case Sql.DateParts.Minute: partStr = "M"; break; case Sql.DateParts.Second: partStr = "S"; break; case Sql.DateParts.Millisecond: builder.Expression = "Cast(strFTime('%f', {date}) * 1000 as int) % 1000"; builder.Extension.Precedence = Precedence.Multiplicative; break; default: throw new ArgumentOutOfRangeException(nameof(part), part, null); } if (partStr != null) { builder.AddExpression("part", partStr); } }
public void Build(Sql.ISqExtensionBuilder builder) { string partStr; var part = builder.GetValue <Sql.DateParts>("part"); switch (part) { case Sql.DateParts.Year: partStr = "year"; break; case Sql.DateParts.Quarter: builder.Expression = "Extract(Month from {date})"; builder.ResultExpression = builder.Inc(builder.Div(builder.Dec(builder.ConvertToSqlExpression(Precedence.Primary)), 3)); return; case Sql.DateParts.Month: partStr = "month"; break; case Sql.DateParts.DayOfYear: partStr = "yearday"; break; case Sql.DateParts.Day: partStr = "day"; break; case Sql.DateParts.Week: partStr = "week"; break; case Sql.DateParts.WeekDay: partStr = "weekday"; break; case Sql.DateParts.Hour: partStr = "hour"; break; case Sql.DateParts.Minute: partStr = "minute"; break; case Sql.DateParts.Second: partStr = "second"; break; case Sql.DateParts.Millisecond: partStr = "millisecond"; break; default: throw new ArgumentOutOfRangeException(); } builder.AddExpression("part", partStr); switch (part) { case Sql.DateParts.DayOfYear: case Sql.DateParts.WeekDay: builder.ResultExpression = builder.Inc(builder.ConvertToSqlExpression(Precedence.Primary)); break; } }
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: partStr = "DayOfWeek({date})"; break; 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: { builder.Expression = "To_Number(To_Char({date}, 'FF'))"; builder.ResultExpression = builder.Div(builder.ConvertToSqlExpression(Precedence.Primary), 1000); return; } default: throw new ArgumentOutOfRangeException(); } builder.Expression = partStr; }
public static ISqlExpression Div(this Sql.ISqExtensionBuilder builder, ISqlExpression expr1, int value) { return(builder.Div <int>(expr1, new SqlValue(value))); }
public static ISqlExpression Div <T>(this Sql.ISqExtensionBuilder builder, ISqlExpression expr1, ISqlExpression expr2) { return(builder.Div(expr1, expr2, 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; }
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(); } }