public void Build(ISqExtensionBuilder builder) { var part = builder.GetValue <DateParts>(0); var startDate = builder.GetExpression(1); var endDate = builder.GetExpression(2); var expStr = part switch { // DateParts.Year => "({1} - {0}) / 365", // DateParts.Month => "({1} - {0}) / 30", DateParts.Week => "(CAST ({1} as DATE) - CAST ({0} as DATE)) / 7", DateParts.Day => "(CAST ({1} as DATE) - CAST ({0} as DATE))", DateParts.Hour => "(CAST ({1} as DATE) - CAST ({0} as DATE)) * 24", DateParts.Minute => "(CAST ({1} as DATE) - CAST ({0} as DATE)) * 1440", DateParts.Second => "(CAST ({1} as DATE) - CAST ({0} as DATE)) * 86400", // this is tempting to use but leads to precision loss on big intervals //DateParts.Millisecond => "1000 * (EXTRACT(SECOND FROM CAST ({1} as TIMESTAMP) - CAST ({0} as TIMESTAMP)) + (CAST ({1} as DATE) - CAST ({0} as DATE)) * 86400)", // could be really ugly on big start/end expressions DateParts.Millisecond => "1000 * (EXTRACT(SECOND FROM CAST ({1} as TIMESTAMP) - CAST ({0} as TIMESTAMP))" + " + 60 * (EXTRACT(MINUTE FROM CAST ({1} as TIMESTAMP) - CAST ({0} as TIMESTAMP))" + " + 60 * (EXTRACT(HOUR FROM CAST ({1} as TIMESTAMP) - CAST ({0} as TIMESTAMP))" + " + 24 * EXTRACT(DAY FROM CAST ({1} as TIMESTAMP) - CAST ({0} as TIMESTAMP)))))", _ => throw new InvalidOperationException($"Unexpected datepart: {part}"), }; builder.ResultExpression = new SqlExpression(typeof(int), expStr, Precedence.Multiplicative, startDate, endDate); }
public void Build(ISqExtensionBuilder builder) { var part = builder.GetValue <DateParts>("part"); var date = builder.GetExpression("date"); var number = builder.GetExpression("number", true); switch (part) { case DateParts.Quarter: part = DateParts.Month; number = builder.Mul(number, 3); break; case DateParts.DayOfYear: case DateParts.WeekDay: part = DateParts.Day; break; case 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(ISqExtensionBuilder builder) { var part = builder.GetValue <DateParts>(0); var startDate = builder.GetExpression(1); var endDate = builder.GetExpression(2); var expStr = "DATEDIFF('"; expStr += part switch { DateParts.Year => "yyyy", DateParts.Quarter => "q", DateParts.Month => "m", DateParts.DayOfYear => "y", DateParts.Day => "d", DateParts.WeekDay => "w", DateParts.Week => "ww", DateParts.Hour => "h", DateParts.Minute => "n", DateParts.Second => "s", DateParts.Millisecond => throw new ArgumentOutOfRangeException(nameof(part), part, "Access doesn't support milliseconds interval."), _ => throw new InvalidOperationException($"Unexpected datepart: {part}"), }; expStr += "', {0}, {1})"; builder.ResultExpression = new SqlExpression(typeof(int), expStr, startDate, endDate); }
public void Build(ISqExtensionBuilder builder) { var part = builder.GetValue <DateParts>(0); var startdate = builder.GetExpression(1); var endDate = builder.GetExpression(2); var divider = 1; string funcName; switch (part) { case DateParts.Day: funcName = "Days_Between"; break; case DateParts.Hour: funcName = "Seconds_Between"; divider = 3600; break; case DateParts.Minute: funcName = "Seconds_Between"; divider = 60; break; case DateParts.Second: funcName = "Seconds_Between"; break; case DateParts.Millisecond: funcName = "Nano100_Between"; divider = 10000; break; default: throw new InvalidOperationException($"Unexpected datepart: {part}"); } ISqlExpression func = new SqlFunction(typeof(int), funcName, startdate, endDate); if (divider != 1) { func = builder.Div(func, divider); } builder.ResultExpression = func; }
public void Build(ISqExtensionBuilder builder) { var part = builder.GetValue <Sql.DateParts>(0); var startDate = builder.GetExpression(1); var endDate = builder.GetExpression(2); var expStr = "round((julianday({1}) - julianday({0}))"; switch (part) { case DateParts.Day: expStr += ")"; break; case DateParts.Hour: expStr += " * 24)"; break; case DateParts.Minute: expStr += " * 1440)"; break; case DateParts.Second: expStr += " * 86400)"; break; case DateParts.Millisecond: expStr += " * 86400000)"; break; default: throw new ArgumentOutOfRangeException(); } builder.ResultExpression = new SqlExpression(typeof(int), expStr, startDate, endDate); }
public void Build(ISqExtensionBuilder builder) { ISqlExpression data; if (builder.Arguments.Length == 2) { data = builder.GetExpression("source"); } else { data = builder.GetExpression("selector"); } // https://github.com/linq2db/linq2db/issues/1765 var descriptor = QueryHelper.GetColumnDescriptor(data); if (descriptor != null) { var dbDataType = descriptor.GetDbDataType(true); if (dbDataType.DataType != DataType.Undefined) { var separator = builder.GetExpression("separator"); if (separator is SqlValue value && value.ValueType.DataType == DataType.Undefined) { value.ValueType = value.ValueType.WithDataType(dbDataType.DataType); } else if (separator is SqlParameter parameter && parameter.Type.DataType == DataType.Undefined) { parameter.Type = parameter.Type.WithDataType(dbDataType.DataType); } } }
public void Build(ISqExtensionBuilder builder) { var part = builder.GetValue <Sql.DateParts>(0); var startDate = builder.GetExpression(1); var endDate = builder.GetExpression(2); string expStr; switch (part) { case DateParts.Year: expStr = "(DATE_PART('year', {1}::date) - DATE_PART('year', {0}::date))"; break; case DateParts.Month: expStr = "((DATE_PART('year', {1}::date) - DATE_PART('year', {0}::date)) * 12 + (DATE_PART('month', {1}'::date) - DATE_PART('month', {0}::date)))"; break; case DateParts.Week: expStr = "TRUNC(DATE_PART('day', {1}::timestamp - {0}::timestamp) / 7)"; break; case DateParts.Day: expStr = "EXTRACT(EPOCH FROM ({1}::timestamp - {0}::timestamp)) / 86400"; break; case DateParts.Hour: expStr = "EXTRACT(EPOCH FROM ({1}::timestamp - {0}::timestamp)) / 3600"; break; case DateParts.Minute: expStr = "EXTRACT(EPOCH FROM ({1}::timestamp - {0}::timestamp)) / 60"; break; case DateParts.Second: expStr = "EXTRACT(EPOCH FROM ({1}::timestamp - {0}::timestamp))"; break; case DateParts.Millisecond: expStr = "ROUND(EXTRACT(EPOCH FROM ({1}::timestamp - {0}::timestamp)) * 1000)"; break; default: throw new ArgumentOutOfRangeException(); } builder.ResultExpression = new SqlExpression(typeof(int), expStr, Precedence.Multiplicative, startDate, endDate); }
public void Build(ISqExtensionBuilder builder) { var part = builder.GetValue <DateParts>("part"); var date = builder.GetExpression("date"); var number = builder.GetExpression("number", true); string expStr = "strftime('%Y-%m-%d', {0},"; switch (part) { case DateParts.Year: expStr += "{1} || ' Year')"; break; case DateParts.Quarter: expStr += "({1}*3) || ' Month')"; break; case DateParts.Month: expStr += "{1} || ' Month')"; break; case DateParts.DayOfYear: case DateParts.WeekDay: case DateParts.Day: expStr += "{1} || ' Day')"; break; case DateParts.Week: expStr += "({1}*7) || ' Day')"; break; default: throw new InvalidOperationException($"Unexpected datepart: {part}"); } builder.ResultExpression = new SqlExpression(typeof(DateTime?), expStr, Precedence.Concatenate, date, number); }
public void Build(ISqExtensionBuilder builder) { var part = builder.GetValue <DateParts>(0); var startdate = builder.GetExpression(1); var endDate = builder.GetExpression(2); var partSql = new SqlExpression(DatePartBuilder.DatePartToStr(part), Precedence.Primary); builder.ResultExpression = new SqlFunction(typeof(int), builder.Expression, partSql, startdate, endDate); }
public void Build(ISqExtensionBuilder builder) { var part = builder.GetValue <Sql.DateParts>("part"); var partStr = DatePartBuilder.DatePartToStr(part); var date = builder.GetExpression("date"); var number = builder.GetExpression("number"); builder.ResultExpression = new SqlFunction(typeof(DateTimeOffset?), builder.Expression, new SqlExpression(partStr, Precedence.Primary), number, date); }
public void Build(ISqExtensionBuilder builder) { var part = builder.GetValue <DateParts>("part"); var date = builder.GetExpression("date"); var number = builder.GetExpression("number", true); string function; switch (part) { case DateParts.Year: function = "Add_Years"; break; case DateParts.Quarter: function = "Add_Months"; number = builder.Mul(number, 3); break; case DateParts.Month: function = "Add_Months"; break; case DateParts.DayOfYear: case DateParts.Day: case DateParts.WeekDay: function = "Add_Days"; break; case DateParts.Week: function = "Add_Days"; number = builder.Mul(number, 7); break; case DateParts.Hour: function = "Add_Seconds"; number = builder.Mul(number, 3600); break; case DateParts.Minute: function = "Add_Seconds"; number = builder.Mul(number, 60); break; case DateParts.Second: function = "Add_Seconds"; break; case DateParts.Millisecond: function = "Add_Seconds"; number = builder.Div(number, 1000); break; default: throw new InvalidOperationException($"Unexpected datepart: {part}"); } builder.ResultExpression = new SqlFunction(typeof(DateTime?), function, date, number); }
public void Build(ISqExtensionBuilder builder) { var fieldExpr = builder.GetExpression(0); var qualified = builder.Arguments.Length <= 1 || builder.GetValue <bool>(1); var isExpression = builder.Member.Name == "FieldExpr"; var field = QueryHelper.ExtractField(fieldExpr); if (field == null) { throw new LinqToDBException($"Can not convert expression {builder.Arguments[0]} to field."); } if (isExpression) { builder.ResultExpression = qualified ? new SqlExpression(typeof(string), "{0}", Precedence.Primary, new SqlField(field)) : new SqlExpression(typeof(string), field.PhysicalName, Precedence.Primary); } else { var name = field.PhysicalName; if (qualified) { name = builder.DataContext.CreateSqlProvider().ConvertInline(name, ConvertType.NameToQueryField); } builder.ResultExpression = new SqlValue(name); } }
public void Build(ISqExtensionBuilder builder) { var expr = builder.GetExpression("expr"); var collation = builder.GetValue <string>("collation").Replace("\"", "\"\""); builder.ResultExpression = new SqlExpression(typeof(string), $"{{0}} COLLATE \"{collation}\"", Precedence.Primary, expr); }
public void Build(ISqExtensionBuilder builder) { var separator = builder.GetExpression(0); var arguments = (NewArrayExpression)builder.Arguments[1]; if (arguments.Expressions.Count == 0) { builder.ResultExpression = new SqlExpression(typeof(string), "''"); } else if (arguments.Expressions.Count == 1) { builder.ResultExpression = IsNullExpression(builder.ConvertExpressionToSql(arguments.Expressions[0])); } else { var items = arguments.Expressions.Select(e => IsNullExpression(StringConcatExpression(separator, builder.ConvertExpressionToSql(e))) ); var concatenation = items.Aggregate(StringConcatExpression); builder.ResultExpression = TruncateExpression(concatenation, separator); } }
public void Build(ISqExtensionBuilder builder) { var expr = builder.GetExpression("expr"); var collation = builder.GetValue <string>("collation"); // collation cannot be parameter builder.ResultExpression = new SqlExpression(typeof(string), $"COLLATION_KEY_BIT({{0}}, {{1}})", Precedence.Primary, expr, new SqlValue(typeof(string), collation)); }
public void Build(ISqExtensionBuilder builder) { var part = builder.GetValue <DateParts>("part"); var partStr = DatePartToStr(part); var date = builder.GetExpression("date"); builder.ResultExpression = new SqlFunction(typeof(int), builder.Expression, new SqlExpression(partStr, Precedence.Primary), date); }
public void Build(ISqExtensionBuilder builder) { var separator = builder.GetExpression("separator"); // SAP HANA doesn't support parameters as separators if (separator is SqlParameter parameter) { parameter.IsQueryParameter = false; } }
public void Build(ISqExtensionBuilder builder) { var part = builder.GetValue <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 DateParts.Day: resultExpr = builder.Div(resultExpr, 86400); break; case DateParts.Hour: resultExpr = builder.Div(resultExpr, 3600); break; case DateParts.Minute: resultExpr = builder.Div(resultExpr, 60); break; case DateParts.Second: break; case 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 InvalidOperationException($"Unexpected datepart: {part}"); } builder.ResultExpression = resultExpr; }
public void Build(ISqExtensionBuilder builder) { var part = builder.GetValue <Sql.DateParts>(0); var startDate = builder.GetExpression(1); var endDate = builder.GetExpression(2); var expStr = "round((julianday({1}) - julianday({0}))"; expStr += part switch { DateParts.Day => ")", DateParts.Hour => " * 24)", DateParts.Minute => " * 1440)", DateParts.Second => " * 86400)", DateParts.Millisecond => " * 86400000)", _ => throw new ArgumentOutOfRangeException(), }; builder.ResultExpression = new SqlExpression(typeof(int), expStr, startDate, endDate); }
public void Build(ISqExtensionBuilder builder) { var part = builder.GetValue <DateParts>(0); var startDate = builder.GetExpression(1); var endDate = builder.GetExpression(2); var expStr = "round((julianday({1}) - julianday({0}))"; expStr += part switch { DateParts.Day => ")", DateParts.Hour => " * 24)", DateParts.Minute => " * 1440)", DateParts.Second => " * 86400)", DateParts.Millisecond => " * 86400000)", _ => throw new InvalidOperationException($"Unexpected datepart: {part}"), }; builder.ResultExpression = new SqlExpression(typeof(int), expStr, startDate, endDate); }
public void Build(ISqExtensionBuilder builder) { var part = builder.GetValue <DateParts>(0); var startDate = builder.GetExpression(1); var endDate = builder.GetExpression(2); var expStr = part switch { DateParts.Year => "(DATE_PART('year', {1}::date) - DATE_PART('year', {0}::date))", DateParts.Month => "((DATE_PART('year', {1}::date) - DATE_PART('year', {0}::date)) * 12 + (DATE_PART('month', {1}'::date) - DATE_PART('month', {0}::date)))", DateParts.Week => "TRUNC(DATE_PART('day', {1}::timestamp - {0}::timestamp) / 7)", DateParts.Day => "EXTRACT(EPOCH FROM ({1}::timestamp - {0}::timestamp)) / 86400", DateParts.Hour => "EXTRACT(EPOCH FROM ({1}::timestamp - {0}::timestamp)) / 3600", DateParts.Minute => "EXTRACT(EPOCH FROM ({1}::timestamp - {0}::timestamp)) / 60", DateParts.Second => "EXTRACT(EPOCH FROM ({1}::timestamp - {0}::timestamp))", DateParts.Millisecond => "ROUND(EXTRACT(EPOCH FROM ({1}::timestamp - {0}::timestamp)) * 1000)", _ => throw new InvalidOperationException($"Unexpected datepart: {part}"), }; builder.ResultExpression = new SqlExpression(typeof(int), expStr, Precedence.Multiplicative, startDate, endDate); }
public void Build(ISqExtensionBuilder builder) { var expr = builder.GetExpression("expr"); var collation = builder.GetValue <string>("collation"); if (!ValidateCollation(collation)) { throw new InvalidOperationException($"Invalid collation: {collation}"); } builder.ResultExpression = new SqlExpression(typeof(string), $"{{0}} COLLATE {collation}", Precedence.Primary, expr); }
public void Build(ISqExtensionBuilder builder) { ISqlExpression data; if (builder.Arguments.Length == 2) { data = builder.GetExpression("source"); } else { data = builder.GetExpression("selector"); } // https://github.com/linq2db/linq2db/issues/1765 if (data is SqlField field && field.Type !.Value.DataType != DataType.Undefined) { var separator = builder.GetExpression("separator"); if (separator is SqlValue value && value.ValueType.DataType == DataType.Undefined) { value.ValueType = value.ValueType.WithDataType(field.Type !.Value.DataType); }
public void Build(ISqExtensionBuilder builder) { var part = builder.GetValue <DateParts>("part"); var date = builder.GetExpression("date"); var number = builder.GetExpression("number", true); string expStr; switch (part) { case DateParts.Year: expStr = "{0} * INTERVAL '1' YEAR"; break; case DateParts.Quarter: expStr = "{0} * INTERVAL '3' MONTH"; break; case DateParts.Month: expStr = "{0} * INTERVAL '1' MONTH"; break; case DateParts.DayOfYear: case DateParts.WeekDay: case DateParts.Day: expStr = "{0} * INTERVAL '1' DAY"; break; case DateParts.Week: expStr = "{0} * INTERVAL '7' DAY"; break; case DateParts.Hour: expStr = "{0} * INTERVAL '1' HOUR"; break; case DateParts.Minute: expStr = "{0} * INTERVAL '1' MINUTE"; break; case DateParts.Second: expStr = "{0} * INTERVAL '1' SECOND"; break; case DateParts.Millisecond: expStr = "{0} * INTERVAL '0.001' SECOND"; break; default: throw new InvalidOperationException($"Unexpected datepart: {part}"); } builder.ResultExpression = builder.Add( date, new SqlExpression(typeof(TimeSpan?), expStr, Precedence.Multiplicative, number), typeof(DateTime?)); }
public void Build(ISqExtensionBuilder builder) { var part = builder.GetValue <DateParts>("part"); var date = builder.GetExpression("date"); var number = builder.GetExpression("number", true); string expStr; switch (part) { case DateParts.Year: expStr = "{0} Year"; break; case DateParts.Quarter: expStr = "({0} * 3) Month"; break; case DateParts.Month: expStr = "{0} Month"; break; case DateParts.DayOfYear: case DateParts.WeekDay: case DateParts.Day: expStr = "{0} Day"; break; case DateParts.Week: expStr = "({0} * 7) Day"; break; case DateParts.Hour: expStr = "{0} Hour"; break; case DateParts.Minute: expStr = "{0} Minute"; break; case DateParts.Second: expStr = "{0} Second"; break; case DateParts.Millisecond: expStr = "({0} / 1000.0) Second"; break; default: throw new InvalidOperationException($"Unexpected datepart: {part}"); } builder.ResultExpression = builder.Add( date, new SqlExpression(typeof(TimeSpan?), expStr, Precedence.Primary, number), typeof(DateTime?)); }
public void Build(ISqExtensionBuilder builder) { var part = builder.GetValue <DateParts>("part"); var date = builder.GetExpression("date"); var number = builder.GetExpression("number", true); string expStr; switch (part) { case DateParts.Year: expStr = "{0} * Interval '1 Year'"; break; case DateParts.Quarter: expStr = "{0} * Interval '1 Month' * 3"; break; case DateParts.Month: expStr = "{0} * Interval '1 Month'"; break; case DateParts.DayOfYear: case DateParts.WeekDay: case DateParts.Day: expStr = "{0} * Interval '1 Day'"; break; case DateParts.Week: expStr = "{0} * Interval '1 Day' * 7"; break; case DateParts.Hour: expStr = "{0} * Interval '1 Hour'"; break; case DateParts.Minute: expStr = "{0} * Interval '1 Minute'"; break; case DateParts.Second: expStr = "{0} * Interval '1 Second'"; break; case DateParts.Millisecond: expStr = "{0} * Interval '1 Millisecond'"; break; default: throw new InvalidOperationException($"Unexpected datepart: {part}"); } builder.ResultExpression = builder.Add( date, new SqlExpression(typeof(TimeSpan?), expStr, Precedence.Multiplicative, number), typeof(DateTime?)); }
public void Build(ISqExtensionBuilder builder) { var tableExpr = builder.GetExpression(0); var fieldName = builder.GetValue <string>(1); var sqlField = tableExpr as SqlField; if (sqlField == null) { throw new LinqToDBException("Can not find Table associated with expression"); } builder.ResultExpression = new SqlField(sqlField.Table !, fieldName); }
public void Build(ISqExtensionBuilder builder) { var tableExpr = builder.GetExpression(0); var sqlField = tableExpr as SqlField; if (sqlField == null) { throw new LinqToDBException("Can not find Table associated with expression"); } var sqlTable = (SqlTable)sqlField.Table !; builder.ResultExpression = new SqlField(sqlTable, sqlTable.TableName.Name); }
public void Build(ISqExtensionBuilder builder) { var part = builder.GetValue <DateParts>("part"); var date = builder.GetExpression("date"); var number = builder.GetExpression("number", true); var partStr = part switch { DateParts.Year => "yyyy", DateParts.Quarter => "q", DateParts.Month => "m", DateParts.DayOfYear => "y", DateParts.Day => "d", DateParts.Week => "ww", DateParts.WeekDay => "w", DateParts.Hour => "h", DateParts.Minute => "n", DateParts.Second => "s", _ => throw new InvalidOperationException($"Unexpected datepart: {part}"), }; builder.ResultExpression = new SqlFunction(typeof(DateTime?), "DateAdd", new SqlValue(partStr), number, date); }
public void Build(ISqExtensionBuilder builder) { var part = builder.GetValue <DateParts>("part"); var date = builder.GetExpression("date"); var number = builder.GetExpression("number", true); string expStr; switch (part) { case DateParts.Year: expStr = "{0} + Interval({1}) Year to Year"; break; case DateParts.Quarter: expStr = "{0} + Interval({1}) Month to Month * 3"; break; case DateParts.Month: expStr = "{0} + Interval({1}) Month to Month"; break; case DateParts.DayOfYear: case DateParts.WeekDay: case DateParts.Day: expStr = "{0} + Interval({1}) Day to Day"; break; case DateParts.Week: expStr = "{0} + Interval({1}) Day to Day * 7"; break; case DateParts.Hour: expStr = "{0} + Interval({1}) Hour to Hour"; break; case DateParts.Minute: expStr = "{0} + Interval({1}) Minute to Minute"; break; case DateParts.Second: expStr = "{0} + Interval({1}) Second to Second"; break; case DateParts.Millisecond: expStr = "{0} + Interval({1}) Second to Fraction * 1000"; break; default: throw new InvalidOperationException($"Unexpected datepart: {part}"); } builder.ResultExpression = new SqlExpression(typeof(DateTime?), expStr, Precedence.Additive, date, number); }