/// <summary>
/// Returns the Db2 compliant sql fragment for performing Date Arithametic.
/// Depending on the parameters, the function will add (Days, Hours, ... , milliseconds)
/// NOTE: DB2 does not support milliseconds; instead it supports microseconds. So the value
/// provided for milliseconds will be multiplied by 100 and defined as microseconds.
/// </summary>
/// <param name="dateDiffInterval">Enumeration of the possible intervals (Days, Hours, Minutes.. MilliSeconds)</param>
/// <param name="duration">If duration is a string, it will be parameterized; otherwise it will be a constant</param>
/// <param name="startDate">If startDate is a string, it will be assumed to be a column name;
/// if it is a dateEnumeration, then it can be either UTC, Local or default.</param>
/// <returns>A code fragment which will perform the appropriate date add operation.</returns>
public override string FormatDateMathSql(EnumDateDiffInterval dateDiffInterval
, object duration
, object startDate)
{
// is the startDate one of the default parameters
string startDateParam = GetDbTimeAs(EnumDateTimeLocale.Default, null);
if (startDate is EnumDateTimeLocale)
{
startDateParam = GetDbTimeAs((EnumDateTimeLocale)startDate, null);
}
if (startDate is string) // columnName
{
startDateParam = startDate.ToString();
}
// determine if the Duration parameter should be a bind variable
// or is a constant
object durationParam = 0; // default is 0 duration.
if (duration is string &&
!string.IsNullOrEmpty(duration.ToString()))
{
BuildBindVariableName(duration.ToString());
}
durationParam = duration;
// db2 does not have milliseconds, substitute milliseconds with Microseconds
return(string.Format("{0} + {1} {2}", startDateParam
, dateDiffInterval == EnumDateDiffInterval.MilliSecond ? string.Format("(100 * {0})", durationParam) : durationParam
, dateDiffInterval == EnumDateDiffInterval.MilliSecond ? "Microsecond" : dateDiffInterval.ToString()));
}
/// <summary>
/// Returns the SqlServer compliant sql fragment for performing Date Arithametic.
/// Depending on the parameters, the function will add (Days, Hours, ... , milliseconds)
/// </summary>
/// <param name="dateDiffInterval">Enumeration of the possible intervals (Days, Hours, Minutes.. MilliSeconds)</param>
/// <param name="duration">If duration is a string, it will be parameterized; otherwise it will be a constant</param>
/// <param name="startDate">If startDate is a string, it will be assumed to be a column name;
/// if it is a dateEnumeration, then it can be either UTC, Local or default.</param>
/// <returns>A code fragment which will perform the appropriate date add operation.</returns>
public override string FormatDateMathSql(EnumDateDiffInterval dateDiffInterval
, object duration
, object startDate)
{
// is the startDate one of the default parameters
string startDateParam = GetDbTimeAs(EnumDateTimeLocale.Default, null);
if (startDate is EnumDateTimeLocale)
{
startDateParam = GetDbTimeAs((EnumDateTimeLocale)startDate, null);
}
if (startDate is string) // columnName
{
startDateParam = startDate.ToString();
}
// determine if the Duration parameter should be a bind variable
// or is a constant
object durationParam = 0; // default is 0 duration.
if (duration is string &&
!string.IsNullOrEmpty(duration.ToString()))
{
BuildBindVariableName(duration.ToString());
}
durationParam = duration;
return(string.Format("DateAdd({0}, {1}, {2})", dateDiffInterval.ToString(), durationParam, startDateParam));
}
/// <summary>
/// Returns the back-end compliant sql fragment for performing Date Arithametic.
/// Depending on the parameters, the function will add (Days, Hours, ... , milliseconds)
/// </summary>
/// <param name="dateDiffInterval">Enumeration of the possible intervals (Days, Hours, Minutes.. MilliSeconds)</param>
/// <param name="duration">If duration is a string, it will be parameterized; otherwise it will be a constant</param>
/// <param name="startDate">If startDate is a string, it will be assumed to be a column name;
/// if it is a dateEnumeration, then it can be either UTC, Local or default.</param>
/// <returns>A code fragment which will perform the appropriate date add operation.</returns>
public override string FormatDateMathSql(EnumDateDiffInterval dateDiffInterval
, object startDate
, object duration)
{
// is the startDate one of the default parameters
string startDateParam = GetDbTimeAs(EnumDateTimeLocale.Default, null);
if (startDate is EnumDateTimeLocale)
{
startDateParam = GetDbTimeAs((EnumDateTimeLocale)startDate, null);
}
if (startDate is string) // columnName
{
startDateParam = startDate.ToString();
}
// determine if the Duration parameter should be a bind variable
// or is a constant
object durationParam = 0; // default is 0 duration.
if (duration is string &&
!string.IsNullOrEmpty(duration.ToString()))
{
BuildBindVariableName(duration.ToString());
}
durationParam = duration;
// do the date math
switch (dateDiffInterval)
{
case EnumDateDiffInterval.Hour:
return(string.Format("({0} + ({1}/24))", startDate, durationParam));
case EnumDateDiffInterval.Minute:
return(string.Format("({0} + ({1}/1440))", startDate, durationParam)); //1440=24*60
case EnumDateDiffInterval.Second:
return(string.Format("({0} + ({1}/86400))", startDate, durationParam)); //=24*60*60
case EnumDateDiffInterval.MilliSecond:
return(string.Format("( to_timestamp( to_char({0}, 'dd-mm-yyyy hh24:mi:ss.') || "
+ " to_char ( to_number( to_char( {0}, 'FF') ) + {1}), 'dd-mm-yyyy hh24:mi:ss.FF'))", startDate, durationParam));
case EnumDateDiffInterval.Day:
default:
return(string.Format("({0} + {1})", startDate, durationParam));
}
}
/// <summary>
/// Returns the back-end compliant sql fragment for performing Date Arithametic.
/// Depending on the parameters, the function will add (Days, Hours, ... , milliseconds)
/// </summary>
/// <param name="dateDiffInterval">Enumeration of the possible intervals (Days, Hours, Minutes.. MilliSeconds)</param>
/// <param name="duration">If duration is a string, it will be parameterized; otherwise it will be a constant</param>
/// <param name="startDate">If startDate is a string, it will be assumed to be a column name;
/// if it is a dateEnumeration, then it can be either UTC, Local or default.</param>
/// <returns>A code fragment which will perform the appropriate date add operation.</returns>
public abstract string FormatDateMathSql(EnumDateDiffInterval dateDiffInterval
, object duration
, object startDate);
