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.

   1: #region Round

   2: /// <summary>

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

   4: /// </summary>

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

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

   7: public static double Round (double value)

   8: {

   9:   double returnValue = 0;

  10:  

  11:   try

  12:   {

  13:     //Convert the value into the string for round.

  14:     string input = Convert.ToString(value);

  15:  

  16:     //Check if the input contains decimal places

  17:     if (input.Contains("."))

  18:     {

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

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

  21:       {

  22:         //Replace the input value with the required value. Added 3 to get only 2 digits after the decimal point.

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

  24:       }

  25:     }

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

  27:     returnValue = Convert.ToDouble(input);

  28:   }

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

  30:  

  31:   return returnValue;

  32: }

  33: #endregion

2. Truncate the decimals through the mathematical operations.

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

   1: #region Round

   2: /// <summary>

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

   4: /// </summary>

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

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

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

   8: public static double Round(double value, byte decimals)

   9: {

  10:   double returnValue = 0;

  11:  

  12:   try

  13:   {

  14:     if (value != 0)

  15:     {

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

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

  18:  

  19:       //Gets the truncated value.

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

  21:     }

  22:   }

  23:   catch

  24:   { throw; }

  25:  

  26:   return returnValue;

  27: }

  28: #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.

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

Rebuild all the Indexes of a SQL Database in one go

Introduction

In my last post, I had explained what could be the best value of Fill Factor for the indexes in SQL Server and had promised to show a handy way to ReBuild all the indexes including the ones created on the Indexed Views.

Implementation

USE DBName

GO

DECLARE @tsql NVARCHAR(MAX)  

DECLARE @fillfactor INT

SET @fillfactor = 70 

SELECT @tsql = 

  STUFF(( SELECT DISTINCT 

           ';' + 'ALTER INDEX ALL ON ' + o.name + ' REBUILD WITH (FILLFACTOR = ' + CONVERT(VARCHAR(3),@fillfactor) + ')'

          FROM 

           sysobjects o 

          INNER JOIN sysindexes i 

           ON o.id = i.id 

          WHERE 

           o.xtype IN ('U','V')

           AND i.name IS NOT NULL

          FOR XML PATH('')), 1,1,'')

--PRINT @tsql          

EXEC sp_executesql @tsql  

Conclusion

This way, we can easily rebuild all the existing indexes on the Tables as well as the Indexed Views of the selected Database with an option to set the fillfactor as well. Hope, this script will prove to be handy.

What is the best value for Fill Factor in SQL Server?

Introduction

When an index is created or rebuilt, the fill-factor value determines the percentage of space on each leaf-level page to be filled with data, reserving the remainder on each page as free space for future growth. For example, specifying a fill-factor value of 80 means that 20 percent of each leaf-level page will be left empty, providing space for index expansion as data is added to the underlying table. The empty space is reserved between the index rows rather than at the end of the index.

The fill-factor value is a percentage from 1 to 100, and the server-wide default is 0, which means that the leaf-level pages are filled to capacity. Fill-factor values 0 and 100 are the same in all respects. The fill-factor setting applies only when the index is created, or rebuilt.

How does it help?

The fill-factor option is provided for fine-tuning index data storage and performance. In an insert-intensive environment, the index pages will eventually split to accommodate additional entries. To avoid or reduce the frequency of page splits, the index should be rebuilt using an appropriate fill factor.

What is the ideal value?

It depends on the ratio of reads to writes that your application makes to your SQL Server tables. As a rule of thumb, we might follow these guidelines:

•          Low Update Tables (100-1 read to write ratio): 100% fill factor
•          High Update Tables (where writes exceed reads): 50%-70% fill factor
•          Everything In-Between: 80%-90% fill factor.

Hope, this small article will help you in identifying the fill-factor for your application/system.

Next, I would come up with a handy script to update the fill-factor of all the existing indexes at one go.

Maximum recursion possible with CTE in SQL Server 2005/2008

In my last blog about Tally Tables, we talked about the use of recursive CTEs to generate Tally Tables. Following that, someone quickly asked me to generate a Tally Table for integers starting from 1 to 150 as when he tried the way explained in my last post, the following exception was generated –

Msg 530, Level 16, State 1, Line 3

The statement terminated. The maximum recursion 100 has been exhausted before statement completion.

Now what to do?

Actually, to prevent infinite recursion, a default value for MAXRECURSION = 100 has been already set. Hence, any recursion will stop on reaching the threshold limit. If we want to loop/iterate more than the default value, we need to set the MAXRECURSION value as explained in my another post - Prevent recursive CTE from entering an infinite loop

So, the following statement will work to generate a Tally Table from 1 to 150–

DECLARE @Max AS INT = 150

;WITH CTE AS (

  SELECT 1 Num

  UNION ALL

  SELECT Num + 1 FROM CTE WHERE Num < @Max

)

SELECT * FROM CTE OPTION (MAXRECURSION 150);

After looking at this option, most of us will ask – what is the max value that could be used with the MAXRECURSION option? And the answer is – 32767. If we try to set a value greater than this, 

SELECT * FROM CTE OPTION (MAXRECURSION 32768);

sql fires the following exception –

Msg 310, Level 15, State 1, Line 10

The value 32768 specified for the MAXRECURSION option exceeds the allowed maximum of 32767.

Hope, this post will make you understand few more facts related to the use of CTE with recursion.

Happy iterating…

CRM 2011: Change sitemap customization

Recently I worked upon a project where in I required updating the sitemap customization to have altogether new areas to be replaced with the default CRM areas and subareas of sitemap. I found some interesting things while doing such customization changes, so I would like to share the same here in this post.

1.       You can give a title to areas and the subareas, suppose I want to change the title of default Sales area from Sales to “Property Sales”, just add a title attribute as shown below.

<Area Title=” Property Sales” Id="SFA" ResourceId="Area_Sales" Icon="/_imgs/sales_24x24.gif" DescriptionResourceId="Sales_Description">

The same attribute could be added for any of the subareas to have different titles.

2.       Setup the custom entity as subarea link,

<SubArea Title="Property Information" Id="nav_property_information" Entity="new_property" />

As shown above, you can setup the custom entity as link. Only the Id is the required attribute. This will show the default view of an entity on click of the above link.

Note: You can use either URL or Entity while setup a custom entity link, if by mistake you keep both the attributes in any of subarea, your CRM server will crash, and you could not browse anything for that particular organization. Also you cannot revert back as there is no “Settings” area available to be browsed now. So I would suggest you to have backup of your database before you import the updated sitemap customization/solution.

Visit below link to know more about the rest of the attributes of subarea.

http://msdn.microsoft.com/en-us/library/gg328498.aspx  

3.       Use the character entity reference, decimal character reference, and hexadecimal character reference for markup-significant and internationalization characters, as well as the rendering of such characters in your browser.

For example, suppose you want a subarea title as “Sales, Marketing & Services”. Use “&amp;” entity in place of ‘&’ character. So now your title will be “Sales, Marketing &amp; Services”.

Visit below link for more help.

http://htmlhelp.com/reference/html40/entities/special.html

New DATETIME Data Types in SQL Server 2008

Introduction

From the beginning, Microsoft SQL Server DBAs have longed for two different data types, where you could store time and date separately. The long wait is over. In all of the older versions, we had only one data type that could store Datetime data type, and it stored both the value of the date and the value of the time.

Finally, in Microsoft SQL Server 2008, Microsoft is introducing a set of new data types for storing date, time and both date and time together. The new data types store more data as well, which means you can store dates anywhere from 01-01-01 to 9999-12-31 and also store time up to the fraction of 9999999.

There are four DATETIME data types newly introduced in SQL Server 2008. They are

DATE, TIME, DATETIMEOFFSET and DATETIME2.

1) DATE Data Type

The DATE data type stores only the date value.

The DATE data type returns data in the form of year, Month and Day. However, the format in which the date is returned is determined by the current language setting in SQL Server.

The range for the DATE data type is from 0001-01-01 through 9999-12-31.

When you specify a datetime value, SQL Server automatically converts that value into the DATE data type, which means that only the date portion is stored.

Example:

DECLARE @Date_Type DATE

SET @Date_Type = '2011-05-17 05:29:15.1234567'

SELECT @Date_Type

Result:

2011-05-17

2) TIME Data type

The TIME data type stores only the time value.

The TIME data type returns data in the form of hour, minute, second and fractional second.

The fractional part of the seconds can support up to 7 decimal places.

The precision of the TIME data type supports a range of 00:00:00.0000000 through 23:59:59.9999999.

When you specify the TIME datatype in a Transact-SQL statement, you can specify the precision of the stored values by including the appropriate number within parentheses.

For example, to specify a precision of 7, you would specify TIME(7).

For a precision of 5, you would specify TIME(5), and so on.

If you do not specify the precision, 7 are assumed.

Example:

DECLARE @Time_Type TIME

SET @Time_Type = '2011-05-17 05:29:15.1234567'

SELECT @Time_Type

Result:

05:29:15.1234567

You can also specify a different precision when converting the data

DECLARE @DateTimeOffset_Type TIME

DECLARE @Time_Type TIME

SET @DateTimeOffset_Type = '2011-05-17 05:29:15.1234567'

SET @Time_Type = CAST(@DateTimeOffset_Type AS TIME(5))

SELECT @Time_Type

Result:

05:29:15.1234600

Notice that the fractional seconds have been rounded up.

The original fractional seconds were .1234567. However, if you specify a precision of 5, SQL Server will round up the "67" fractional part, returning .1234600 in the results, rather than .1234500.

When inserting data into a TIME column, you can specify a time value or a datetime value as shown in the following INSERT statements:

INSERT INTO TableName (ID, Time_DataType)

VALUES ('1004, 05:23:10.1234567'); --Only Time

INSERT INTO TableName (ID, Time_DataType)

VALUES (1005, '2011-05-17 05:23:10.1234567'); --Date & Time

In both cases, only the time is inserted into the Time_DataType column.

3) DATETIME2 Data Type

The DATETIME2 data type stores the both date and time value.

The data type DATETIME2 is the combination of the data type DATE and TIME.

DATETIME2 is used to store both a date value ranging from 01-01-01 to 9999-12-31 as well as a time value 00:00:00.0000000 to 23:59:59.9999999.

Example:

DECLARE @DateTime2_Type DATETIME2

SET @DateTime2_Type = CONVERT(DATETIME2,'01/01/0001 16:14:00.1234567')

SELECT @DateTime2_Type

Result:

0001-01-01 16:14:00.1234567

We could limit the precision like (Precision value range 0-7),

DECLARE @DateTime2_Type DATETIME2(4)

SET @DateTime2_Type = CONVERT(DATETIME2,'01/01/0001 16:14:00.1234567')

SELECT @DateTime2_Type

Result:

0001-01-01 16:14:00.1235

Notice that the fractional seconds have been rounded up.

4) DATETIMEOFFSET Data type

The DATETIMEOFFSET data type stores the date, time and time-zone offset value.

The DATETIMEOFFSET data type is nearly identical to the DATETIME2 data type, except that a DATETIMEOFFSET value includes one important addition that is a time-zone offset value.

The offset value represents the number of hours and minutes before or after Coordinated Universal Time (UTC).

A positive number indicates the amount of time to add to the UTC to determine the local time. (+05:30).

A negative number indicates the amount of time to subtract from the UTC to determine the local time. (-05:30)

The Date range is between 0001-01-01 and 9999-12-31.

The Time Range is between 00:00:00 and 23:59:59.9999999.

The Offset range is between -14:00 through +14:00.

Example:

DECLARE @DateTimeOffset_Type DATETIMEOFFSET

SET @DateTimeOffset_Type = CONVERT(DATETIMEOFFSET,'01/01/0001 16:14:00.1234567 +5:30')

SELECT @DateTimeOffset_Type

Result:

0001-01-01 16:14:00.1234567 +05:30

You can also set the precision of the data type, like

DECLARE @DateTimeOffset_Type DATETIMEOFFSET(4)

SET @DateTimeOffset_Type = CONVERT(DATETIMEOFFSET,'01/01/0001 16:14:00.1234567 +5:30')

SELECT @DateTimeOffset_Type

Result:

0001-01-01 16:14:00.1235 +05:30

Summary:

Data type	Format	Range	Accuracy	Storage size (bytes)	User-defined fractional second precision	Time zone offset
TIME	hh:mm:ss[.nnnnnnn]	00:00:00.0000000 to 23:59:59.9999999	100 nanoseconds	3 to 5	Yes	No
DATE	YYYY-MM-DD	00001-01-01 to 9999-12-31	1 day	3	No	No
SMALLDATETIME	YYYY-MM-DD hh:mm:ss	1900-01-01 to 2079-06-06	1 minute	4	No	No
DATETIME	YYYY-MM-DD hh:mm:ss[.nnn]	1753-01-01 to 9999-12-31	0.333 second	8	No	No
DATETIME2	YYYY-MM-DD hh:mm:ss[.nnnnnnn]	0001-01-01 00:00:00.0000000 to 9999-12-31 23:59:59.9999999	100 nanoseconds	6 to 8	Yes	No
DATETIMEOFFSET	YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm	00001-01-01 00:00:00.0000000 to 9999-12-31 23:59:59.9999999 (in UTC)	100 nanoseconds	8 to 10	Yes	Yes