New Date and Time Functions in SQL Server 2008

Introduction

SQL Server 2008 introduces five new date and time functions namely SYSDATETIME, SYSDATETIMEOFFSET, SYSUTCDATETIME, SWITCHOFFSET and TODATETIMEOFFSET.

In this article described new functions in detail.

1)   SYSDATETIME

The SYSDATETIME function basically returns the current system timestamp without the time zone information.

SELECT SYSDATETIME()

GO

SYSDATETIME

------------------------

2011-06-27 16:06:15.9409129

2)   SYSDATETIMEOFFSET

The SYSDATETIMEOFFSET function is much similar to SYSDATETIME function; however it will also get you the time zone information.

SELECT SYSDATETIMEOFFSET()

GO

SYSDATETIMEOFFSET

-------------------------------

2011-06-27 16:07:01.8205935 +05:30

3)   SYSUTCDATETIME

The SYSUTCDATETIME function returns the Greenwich Mean Time (GMT) or the Coordinated Universal Time (UTC). This time is derived from the current local system time and the time zone settings of the server where SQL Server 2008 is running.

SELECT SYSUTCDATETIME()

GO

SYSUTCDATETIME

------------------------

2011-06-27 10:38:25.6239407

4)   SWITCHOFFSET

The SWITCHOFFSET function returns a DATETIMEOFFSET value that is changed from the stored time zone offset to a specified new time zone offset.

SELECT SYSDATETIMEOFFSET(),

       SWITCHOFFSET (SYSDATETIMEOFFSET(), '+00:00')

GO

SYSDATETIMEOFFSET                                            SWITCHOFFSET

-------------------------------                                        -------------------------------------

2011-06-27 16:30:55.7127120 +05:30           2011-06-27 11:00:55.7127120 +00:00

5)   TODATETIMEOFFSET

The TODATETIMEOFFSET function returns a DATETIMEOFFSET value that is translated from a DATETIME2 expression.

SELECT TODATETIMEOFFSET (GETDATE(),'+05:30')

GO

TODATETIMEOFFSET

---------------------------------

2011-06-27 16:26:56.340 +05:30

Truncate decimals instead of Rounding from a double value In C#

Introduction

Many times while working on applications, we often land into a situation where we want to truncate the decimal places from a number instead of round like suppose we have 5.329 and I want to truncate the third digit then my required output will be 5.32. Here, I will walk through the different ways which I know in which we can achieve the same.

Approaches

I have created the approaches as making the C# method.

1.      Truncate the decimals through the string operations.

Below method only usable if you want to truncate the decimal places to two.

#region Round

/// <summary>

/// Truncate the last digit after two digit floating points.

/// </summary>

/// <param name="value">A double-precision floating-point number to be rounded.</param>

/// <returns>Double value contains two digit floating points.</returns>

public static double Round (double value)

{

  double returnValue = 0;

  try

  {

    //Convert the value into the string for round.

    string input = Convert.ToString(value);

    //Check if the input contains decimal places

    if (input.Contains("."))

    {

      //Check if there are 3 or more digits after the decimal point.

      if (input.Length - input.IndexOf(".", StringComparison.Ordinal) >= 4)

      {

        //Replace the input value with the required value.

        //Added 3 to get only 2 digits after the decimal point.

        input = input.Substring(0, input.IndexOf(".", StringComparison.Ordinal) + 3);

      }

    }

        //Converts the string representation of a number to its System.Double equivalent.

    returnValue = Convert.ToDouble(input);

  }

  catch { } //Suppress exception no need to handle.

  return returnValue;

}

#endregion

2.      Truncate the decimals through the mathematical operations.

Through this method you can truncate any number of decimal places.

#region Round

/// <summary>

/// Rounds a decimal value to a specified number of fractional digits.

/// </summary>

/// <param name="value">A decimal number to be rounded.</param>

/// <param name="decimals">The number of decimal places in the return value.</param>

/// <returns>The number nearest to value that contains a number of fractional digits equal to decimals.</returns>

public static double Round(double value, byte decimals)

{

  double returnValue = 0;

  try

  {

    if (value != 0)

    {

      //Gets the power of 10 base on passed decimal parameter value (10^decimals).

      double powValue = Math.Pow(10, decimals);

      //Gets the truncated value.

      returnValue = Math.Truncate(CDCommon.ToDouble(value * powValue)) / powValue;

    }

  }

  catch

  { throw; }

  return returnValue;

}

#endregion

Conclusion

From both the ways, as per my opinion prefer the second approach in implementation because it’s very light compared to the first in terms of calculations.