public void ExtractTest() //TODO: Determine how to extract. Use .NET? (Malisa) { SqlSelect select = SqlDml.Select(); select.Columns.Add(SqlDml.Extract(SqlDateTimePart.Day, "2006-01-23")); Console.WriteLine(sqlDriver.Compile(select).GetCommandText()); }
private static SqlExpression DateTimeOffsetTimeOfDay(SqlExpression dateTimeOffset) { return(SqlDml.Extract(SqlDateTimeOffsetPart.Hour, dateTimeOffset) * (60 * 60 * NanosecondsPerSecond) + SqlDml.Extract(SqlDateTimeOffsetPart.Minute, dateTimeOffset) * (60 * NanosecondsPerSecond) + SqlDml.Extract(SqlDateTimeOffsetPart.Second, dateTimeOffset) * NanosecondsPerSecond + SqlDml.Extract(SqlDateTimeOffsetPart.Millisecond, dateTimeOffset) * NanosecondsPerMillisecond); }
private void VisitDateTimeOffset(SqlExtract node) { switch (node.DateTimeOffsetPart) { case SqlDateTimeOffsetPart.Date: DateTimeTruncate(DateTimeOffsetExtractDateTimeAsString(node.Operand)).AcceptVisitor(this); return; case SqlDateTimeOffsetPart.DateTime: DateTime(DateTimeOffsetExtractDateTimeAsString(node.Operand)).AcceptVisitor(this); return; case SqlDateTimeOffsetPart.LocalDateTime: DateTimeOffsetToLocalDateTime(node.Operand).AcceptVisitor(this); return; case SqlDateTimeOffsetPart.UtcDateTime: DateTimeOffsetToUtcDateTime(node.Operand).AcceptVisitor(this); return; case SqlDateTimeOffsetPart.Offset: (DateTimeOffsetExtractOffsetAsTotalNanoseconds(node.Operand)).AcceptVisitor(this); return; case SqlDateTimeOffsetPart.TimeZoneHour: (DateTimeOffsetExtractOffsetAsTotalNanoseconds(node.Operand) / NanosecondsPerHour).AcceptVisitor(this); return; case SqlDateTimeOffsetPart.TimeZoneMinute: (((DateTimeOffsetExtractOffsetAsTotalNanoseconds(node.Operand)) % NanosecondsPerHour) / (60 * NanosecondsPerSecond)).AcceptVisitor(this); return; } Visit(SqlDml.Extract(ConvertDateTimeOffsetPartToDateTimePart(node.DateTimeOffsetPart), DateTimeOffsetExtractDateTimeAsString(node.Operand))); }
public void ExtractTest() { SqlSelect select = SqlDml.Select(); select.Columns.Add(SqlDml.Extract(SqlDateTimePart.Day, "2006-01-23")); Console.WriteLine(SqlDriver.Compile(select).GetCommandText()); }
public virtual void ExtractNanosecondPartTest() { if (IsNanosecondSupported) { CheckEquality(SqlDml.Extract(SqlDateTimeOffsetPart.Nanosecond, DefaultDateTimeOffset), TryTranformToLocalZone(DefaultDateTimeOffset).Millisecond * 1000000); } }
public virtual void DateTimeExtractDayTest() { var select = SqlDml.Select(); select.Columns.Add( SqlDml.Extract(SqlDateTimePart.Day, new DateTime(2006, 5, 4))); select.Columns.Add(4); }
protected virtual SqlExpression DateTimeTruncate(SqlExpression date) { return(DateAddMillisecond(DateAddSecond(DateAddMinute(DateAddHour(date, -SqlDml.Extract(SqlDateTimePart.Hour, date)), -SqlDml.Extract(SqlDateTimePart.Minute, date)), -SqlDml.Extract(SqlDateTimePart.Second, date)), -SqlDml.Extract(SqlDateTimePart.Millisecond, date))); }
public virtual void IntervalExtractMillisecondTest() { var select = SqlDml.Select(); select.Columns.Add( SqlDml.Extract(SqlIntervalPart.Millisecond, new TimeSpan(6, 5, 4, 3, 2))); select.Columns.Add(2); }
public virtual void IntervalExtractHourTest() { var select = SqlDml.Select(); select.Columns.Add( SqlDml.Extract(SqlIntervalPart.Hour, new TimeSpan(6, 5, 4, 3, 2))); select.Columns.Add(5); }
public virtual void DateTimeExtractDayOfYearTest() { var select = SqlDml.Select(); select.Columns.Add( SqlDml.Extract(SqlDateTimePart.DayOfYear, new DateTime(2005, 2, 2))); select.Columns.Add(33); }
public virtual void DateTimeExtractDayOfWeekTest() { var select = SqlDml.Select(); select.Columns.Add( SqlDml.Extract(SqlDateTimePart.DayOfWeek, new DateTime(2009, 3, 2))); select.Columns.Add((int)DayOfWeek.Monday); }
public virtual void DateTimeExtractMillisecondTest() { var select = SqlDml.Select(); select.Columns.Add( SqlDml.Extract(SqlDateTimePart.Millisecond, new DateTime(2006, 5, 4, 3, 2, 1, 333))); select.Columns.Add(333); }
public virtual void DateTimeExtractHourTest() { var select = SqlDml.Select(); select.Columns.Add( SqlDml.Extract(SqlDateTimePart.Hour, new DateTime(2006, 5, 4, 3, 2, 1, 333))); select.Columns.Add(3); }
public virtual void DateTimeExtractYearTest() { var select = SqlDml.Select(); select.Columns.Add( SqlDml.Extract(SqlDateTimePart.Year, new DateTime(2006, 5, 4))); select.Columns.Add(2006); CompareColumnEquality(select); }
public void Test014_9() { string nativeSql = @"select strftime('%H', 'now') AS Hour "; SqlSelect select = SqlDml.Select(); select.Columns.Add(SqlDml.Extract(SqlDateTimePart.Hour, "NOW"), "Hour"); Assert.IsTrue(CompareExecuteDataReader(nativeSql, select)); }
public void Test014_6() { string nativeSql = @"select strftime('%d', 'now') AS DayOfMonth "; SqlSelect select = SqlDml.Select(); select.Columns.Add(SqlDml.Extract(SqlDateTimePart.Day, "NOW"), "DayOfMonth"); Assert.IsTrue(CompareExecuteDataReader(nativeSql, select)); }
public void Test014_12() { string nativeSql = @"select strftime('%f', 'now') AS Milliseconds "; SqlSelect select = SqlDml.Select(); select.Columns.Add(SqlDml.Extract(SqlDateTimePart.Millisecond, "NOW"), "Milliseconds"); Assert.IsTrue(CompareExecuteDataReader(nativeSql, select)); }
/// <summary> /// Converts the specified interval expression to expression /// that represents number of milliseconds in that interval. /// This is a generic implementation via <see cref="SqlExtract"/>s. /// It's suitable for any server, but can be inefficient. /// </summary> /// <param name="interval">The interval to convert.</param> /// <returns>Result of conversion.</returns> public static SqlExpression IntervalToMilliseconds(SqlExpression interval) { var days = SqlDml.Extract(SqlIntervalPart.Day, interval); var hours = SqlDml.Extract(SqlIntervalPart.Hour, interval); var minutes = SqlDml.Extract(SqlIntervalPart.Minute, interval); var seconds = SqlDml.Extract(SqlIntervalPart.Second, interval); var milliseconds = SqlDml.Extract(SqlIntervalPart.Millisecond, interval); return((((days * 24L + hours) * 60L + minutes) * 60L + seconds) * 1000L + milliseconds); }
private static SqlExpression IntervalToIsoString(SqlExpression interval, bool signed) { if (!signed) { return(SqlDml.FunctionCall("TO_CHAR", interval, "HH24:MI")); } var hours = SqlDml.FunctionCall("TO_CHAR", SqlDml.Extract(SqlIntervalPart.Hour, interval), "SG09"); var minutes = SqlDml.FunctionCall("TO_CHAR", SqlDml.Extract(SqlIntervalPart.Minute, interval), "FM09"); return(SqlDml.Concat(hours, ":", minutes)); }
private static SqlExpression DateTimeOffsetTruncate(SqlExpression dateTimeOffset) { return(SqlDml.Cast( DateAddMillisecond( DateAddSecond( DateAddMinute( DateAddHour(dateTimeOffset, -SqlDml.Extract(SqlDateTimeOffsetPart.Hour, dateTimeOffset)), -SqlDml.Extract(SqlDateTimeOffsetPart.Minute, dateTimeOffset)), -SqlDml.Extract(SqlDateTimeOffsetPart.Second, dateTimeOffset)), -SqlDml.Extract(SqlDateTimeOffsetPart.Millisecond, dateTimeOffset)), SqlType.DateTime)); }
public static SqlExpression DateTimeDayOfWeek(SqlExpression _this) { var baseExpression = ExpressionTranslationHelpers.ToInt(SqlDml.Extract(SqlDateTimePart.DayOfWeek, _this)); var context = ExpressionTranslationContext.Current; if (context == null) { return(baseExpression); } if (context.ProviderInfo.ProviderName == WellKnown.Provider.MySql) { return(baseExpression - 1); //Mysql starts days of week from 1 unlike in .Net. } return(baseExpression); }
public void Test024() { string nativeSql = @"SELECT YEAR(r.rental_date) as Year, COUNT(*) Rented FROM rental r GROUP BY YEAR(r.rental_date)"; SqlTableRef rental = SqlDml.TableRef(schema.Tables["rental"], "r"); SqlSelect select = SqlDml.Select(rental); select.Columns.Add(SqlDml.Extract(SqlDateTimePart.Year, rental["rental_date"]), "Year"); select.Columns.Add(SqlDml.Count(), "Rented"); select.GroupBy.Add(SqlDml.Extract(SqlDateTimePart.Year, rental["rental_date"])); Assert.IsTrue(CompareExecuteDataReader(nativeSql, select)); }
public void Test024() { string nativeSql = @"SELECT strftime('%Y', PaymentDate) as Year, COUNT(*) Required FROM invoice r GROUP BY strftime('%Y', PaymentDate)"; SqlTableRef invoice = SqlDml.TableRef(schema.Tables["invoice"], "r"); SqlSelect select = SqlDml.Select(invoice); select.Columns.Add(SqlDml.Extract(SqlDateTimePart.Year, invoice["PaymentDate"]), "Year"); select.Columns.Add(SqlDml.Count(), "Required"); select.GroupBy.Add(SqlDml.Extract(SqlDateTimePart.Year, invoice["PaymentDate"])); Assert.IsTrue(CompareExecuteDataReader(nativeSql, select)); }
/// <summary> /// Converts the specified interval expression to expression /// that represents number of milliseconds in that interval. /// This is a generic implementation via <see cref="SqlExtract"/>s. /// It's suitable for any server, but can be inefficient. /// </summary> /// <param name="interval">The interval to convert.</param> /// <returns>Result of conversion.</returns> public static SqlExpression IntervalToNanoseconds(SqlExpression interval) { var nanoseconds = SqlDml.Extract(SqlIntervalPart.Nanosecond, interval); return(IntervalToMilliseconds(interval) * 1000000L + nanoseconds); }
public static SqlExpression DateTimeDayOfYear(SqlExpression _this) { return(ExpressionTranslationHelpers.ToInt(SqlDml.Extract(SqlDateTimePart.DayOfYear, _this))); }
public static SqlExpression DateTimeMillisecond(SqlExpression _this) { return(ExpressionTranslationHelpers.ToInt(SqlDml.Extract(SqlDateTimePart.Millisecond, _this))); }
public static SqlExpression TimeSpanDays(SqlExpression _this) { return(ExpressionTranslationHelpers.ToInt(SqlDml.Extract(SqlIntervalPart.Day, _this))); }
public static SqlExpression TimeSpanMilliseconds(SqlExpression _this) { return(ExpressionTranslationHelpers.ToInt(SqlDml.Extract(SqlIntervalPart.Millisecond, _this))); }
public virtual void DateTimeExtractDayTest() { CheckEquality( SqlDml.Extract(SqlDateTimePart.Day, DefaultDateTime), DefaultDateTime.Day); }
public virtual void DateTimeExtractMonthTest() { CheckEquality( SqlDml.Extract(SqlDateTimePart.Month, DefaultDateTime), DefaultDateTime.Month); }