How to create a mounted drive

How to create a mounted drive

To mount a volume:

1. Click Start, click Run, and then type compmgmt.msc in the Open box.
2. In the left pane, click Disk Management.
3. Right-click the partition or volume that you want to mount, and then click Change Drive Letter and Paths.
4. Click Add.
5. Click Mount in the following empty NTFS folder (if it is not already selected), and then use one of the following steps:
• Type the path to an empty folder on an NTFS volume, and then click OK.
• Click Browse, locate the empty NTFS folder, click OK, and then click OK.
• If you have not yet created an empty folder, click Browse, click New Folder to create an empty folder on an NTFS volume, type a name for the new folder, click OK, and then click OK.
6. Quit the Disk Management snap-in.

How to remove a mounted drive

To remove a mounted volume:

1. Click Start, click Run, and then type compmgmt.msc in the Open box.
2. In the left pane, click Disk Management.
3. Right-click the partition or volume that you want to unmount, and then click Change Drive Letter and Paths.
4. Click the mounted drive path that you want to remove, and then click Remove.
5. Click Yes when you are prompted to remove the drive path.
6. Quit the Disk Management snap-in.

 

Sorting and Searching Using C# Lists

It is a fairly common programming scenario to find ourselves with a list of identical objects. In the past, without adequate support from programming languages, we found ourselves writing a lot of searching and sorting code, and that may have put you off using lists in favour of arrays. All that has changed with C# 2.0 - its implementation of a list makes handling such lists remarkably easy. For example, given the following class Person:

   public class Person

        {

            public int age;

            public string name;

            public Person(int age, string name)

            {

                this.age = age;

                this.name = name;

            }

        }

We can create a list of Person objects and add six people like so:

List<Person> people = new List<Person>();

people.Add(new Person(50, "Fred"));

people.Add(new Person(30, "John"));

people.Add(new Person(26, "Andrew"));

people.Add(new Person(24, "Xavier"));

people.Add(new Person(5, "Mark"));

people.Add(new Person(6, "Cameron"));

C# 2.0's list mechanism provides us with a number of useful methods. Personally, I find ForEach, FindAll and Sort to be very useful. ForEach allows us access to each item in the list. FindAll allows us to search for objects in the list that match a specific condition. Sort allows us to sort the objects in the list. The following code demonstrates how we might use each of these methods:

Console.WriteLine("Unsorted list");

people.ForEach(delegate(Person p) { Console.WriteLine(String.Format("{0} {1}", p.age, p.name)); });

List<Person> young = people.FindAll(delegate(Person p) { return p.age < 25; });

Console.WriteLine("Age is less than 25");

young.ForEach(delegate(Person p) { Console.WriteLine(String.Format("{0} {1}", p.age, p.name)); });

Console.WriteLine("Sorted list, by name");

people.Sort(delegate(Person p1, Person  p2) { return p1.name.CompareTo(p2.name); });

people.ForEach(delegate(Person p) { Console.WriteLine(String.Format("{0} {1}", p.age, p.name)); });

people.Sort(delegate(Person p1, Person  p2) { return p1.age.CompareTo(p2.age); });

Console.WriteLine("Sorted list, by age");

people.ForEach(delegate(Person p) { Console.WriteLine(String.Format("{0} {1}", p.age, p.name)); });

And here is the output that we should expect:

Unsorted list

50 Fred

30 John

26 Andrew

24 Xavier

5 Mark

6 Cameron

Age is less than 25

24 Xavier

5 Mark

6 Cameron

Sorted list, by name

26 Andrew

6 Cameron

50 Fred

30 John

5 Mark

24 Xavier

Sorted list, by age

5 Mark

6 Cameron

24 Xavier

26 Andrew

30 John

50 Fred

Lists are powerful and result in fewer, and more elegant, lines of code. Hopefully this short example has demonstrated their ease and you will find yourself using them in your day-to-day development activities.

 

View all constraint of table in SQL Server.

Query is to view all constraints of the table in SQL Server.

 

 

SELECT * FROM (

Select SysObjects.[Name] As [Contraint Name] ,Tab.[Name] as [Table Name],Col.[Name] As [Column Name]

 From SysObjects Inner Join (Select [Name],[ID] From SysObjects Where XType = 'U') As Tab

On Tab.[ID] = Sysobjects.[Parent_Obj]

Inner Join sysconstraints On sysconstraints.Constid = Sysobjects.[ID]

Inner Join SysColumns Col On Col.[ColID] = sysconstraints.[ColID] And Col.[ID] = Tab.[ID]

 ) A WHERE A.[TABLE NAME]='TABLENAME'

 

 

 

Thanks,

Siddu

 

P Please consider the environment before printing this e-mail

 

 

Defining SQL Server constraints with TSQL

 

Types of constraints

I focus on four types of constraints: primary key, foreign key, unique, and check. Here's a brief overview of each.

Primary key
This constraint is used to guarantee that a column or set of columns on a table contain unique values for every record in the given table. This lets you ensure data integrity by always being able to uniquely identify the record in the table.

Get SQL tips in your inbox

TechRepublic's SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system.

Automatically sign up today!

A table can have only one primary key constraint defined on it, and the rows in the primary key columns cannot contain null values. A primary key constraint can be defined when a table is created, or it can be added later.

This script creates a primary key constraint on a single field when the table is created:

IF OBJECT_ID('SalesHistory')>0
       DROP TABLE SalesHistory;
 
 

GO
 
 

CREATE TABLE [dbo].[SalesHistory](
       [SaleID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
       [Product] [char](150) NULL,
       [SaleDate] [datetime] NULL,
       [SalePrice] [money] NULL
 )
 
 

GO

The followings script creates the primary key constraint when the table is created. This method allows you to define a name for the constraint and to create the constraint on multiple columns if necessary.

IF OBJECT_ID('SalesHistory')>0
       DROP TABLE SalesHistory;
 
 

GO
 CREATE TABLE [dbo].[SalesHistory](
       [SaleID] [int] IDENTITY(1,1) NOT NULL,
       [Product] [char](150) NULL,
       [SaleDate] [datetime] NULL,
       [SalePrice] [money] NULL, 
       CONSTRAINT pk_SaleID PRIMARY KEY (SaleID)
 
 

)
 
 

GO

This script creates the primary key constraint on the table after it is created:

IF OBJECT_ID('SalesHistory')>0
       DROP TABLE SalesHistory;
 
 

GO
 
 

CREATE TABLE [dbo].[SalesHistory](
       [SaleID] [int] IDENTITY(1,1) NOT NULL,
       [Product] [char](150) NULL,
       [SaleDate] [datetime] NULL,
       [SalePrice] [money] NULL
 
 

)
 
 

GO
 
 

ALTER TABLE SalesHistory
 
 

ADD CONSTRAINT  pk_SaleID PRIMARY KEY (SaleID)
 
 

GO

Foreign key
This constraint limits the values of columns in one table based upon the values of columns in another table. This link between the two tables requires the use of a "lookup table," which contains the accepted list of values; this list must contain a unique or primary key constraint. After the constraint is established between the two tables, any data modifications to the fields defined in the constraint on the foreign key table will cause a validation to ensure that the data being updated or inserted is contained in the lookup table.

The script in Listing A creates a ProductTypes table, which will serve as the lookup table and the SalesHistory table, which will reference the ProductID in the ProductTypes table. If I had excluded the constraint definition in the table declaration, I could go back later and add it. You can do this with the script in Listing B.

The previous script contains the WITH NOCHECK clause. I use it so that any existing values in the table are not considered when the constraint is added. Any records in the table that violate the newly added constraint will be ignored so that the constraint is created. The constraint will only be applicable to new records entered into the SalesHistory table.

Unique
This constraint guarantees that the values in a column or set of columns are unique. Unique and primary key constraints are somewhat similar because each provide a guarantee for uniqueness for a column or set of columns. A primary key constraint automatically has a unique constraint defined on it.

There are two differences between the constraints: (1) You may have only one primary key constraint per table, yet you may have many unique constraints per table; (2) A primary key constraint will not allow null values but a unique constraint will (although it will only allow one null value per field).

This script creates a unique constraint on the SaleID column when the table is created:

IF OBJECT_ID('SalesHistory')>0
       DROP TABLE SalesHistory;
 
 

GO
 CREATE TABLE [dbo].[SalesHistory](
       [SaleID] [int] NOT NULL UNIQUE,
       [Product] [char](150) NULL,
       [SaleDate] [datetime] NULL,
       [SalePrice] [money] NULL
 
 

)
 
 

GO

The following script creates a unique constraint on the table at creation, and it allows for constraint naming and for defining the unique constraint on multiple columns if necessary.

IF OBJECT_ID('SalesHistory')>0
       DROP TABLE SalesHistory;
 
 

GO
 
 

CREATE TABLE [dbo].[SalesHistory](
       [SaleID] [int]  NOT NULL,
       [Product] [char](150) NULL,
       [SaleDate] [datetime] NULL,
       [SalePrice] [money] NULL, 
       CONSTRAINT uc_SaleID UNIQUE (SaleID)
 
 

)
 
 

GO

This script creates the unique constraint on the SalesHistory table by altering the table after it has been created:

IF OBJECT_ID('SalesHistory')>0
       DROP TABLE SalesHistory;
 
 

GO
 
 

CREATE TABLE [dbo].[SalesHistory](
       [SaleID] [int] NOT NULL,
       [Product] [char](150) NULL,
       [SaleDate] [datetime] NULL,
       [SalePrice] [money] NULL
 
 

)
 
 

GO
 
 

ALTER TABLE SalesHistory
 
 

ADD CONSTRAINT  uc_SaleID UNIQUE(SaleID)
 
 

GO

Check
This constraint limits the value range, or domain, in a column. Check constraints check the acceptable values against a logical expression defined in the constraint. These constraints are similar to foreign key constraints in that they both govern the acceptable values for a column or set of columns in a given row in a table. You can create a check constraint at the column or table level. A check constraint on a single column allows only certain values for those columns, while a table check constraint can limit values in certain columns based on values in other fields in the row.

The following script creates a check constraint on the SalePrice column in the SalesHistory table, limiting entries where the SalePrice must be greater than 4. Any attempt to enter a record with the SalePrice present and less than 4 will result in an error.

IF OBJECT_ID('SalesHistory')>0
       DROP TABLE SalesHistory;
 
 

GO
 
 

CREATE TABLE [dbo].[SalesHistory](
       [SaleID] [int]  NOT NULL,
       [Product] [char](150) NULL,
       [SaleDate] [datetime] NULL,
       [SalePrice] [money] NULL CHECK (SalePrice > 4)
 
 

)
 
 

GO

The script in Listing C creates a check constraint on the SalesHistory table, limiting the SalePrice to be greater than 10 and the Product field to have the value Computer. This isn't a very practical constraint, but it does illustrate how you can set constraints on multiple columns for a table. Listing D is effectively the same as the script in Listing C, but it defines the constraint after the table is created.

 

How to import Excel XML spreadsheets without interop

How to import Excel XML spreadsheets without interop

Simple way how to import XML Excel spreadsheets into DataTable

Did you need to import Excel spreadsheet ever? If you did you know there're sometimes problems with interop assemblies deploying to desktops and so. Office 2003 offers to store spreadsheets in XML. So store your spreadsheet as XML and try to import. Just include ExcelReader.cs in your project and use SPOTX namespace. Then you'll be able to add imported DataTable as datasource to your grid like in included exampe.

using System;

using System.Collections.Generic;

using System.Text;

using System.Xml;

using System.Data;

namespace SPOTX

{

    /// <summary>

    /// Static methods for reading XML schema

    /// </summary>

    class ExcelReader

    {

        /// <summary>

        /// Main static method which imports XML spreadsheet into DataTable

        /// </summary>

        /// <param name="ExcelXmlFile">Imported file</param>

        /// <returns>dataTable result</returns>

        public static DataTable ReadExcelXML(string ExcelXmlFile)

        {

            DataTable dt = new DataTable();

            XmlDocument xc = new XmlDocument();

            xc.Load(ExcelXmlFile);

            XmlNamespaceManager nsmgr = new XmlNamespaceManager(xc.NameTable);

            nsmgr.AddNamespace("o", "urn:schemas-microsoft-com:office:office");

            nsmgr.AddNamespace("x", "urn:schemas-microsoft-com:office:excel");

            nsmgr.AddNamespace("ss", "urn:schemas-microsoft-com:office:spreadsheet");

            XmlElement xe = (XmlElement)xc.DocumentElement.SelectSingleNode("//ss:Worksheet/ss:Table", nsmgr);

            if (xe == null)

                return null;

            XmlNodeList xl = xe.SelectNodes("ss:Row", nsmgr);

            int Row = -1, Col = 0;

            Dictionary<int, string> cols = new Dictionary<int, string>();

            foreach (XmlElement xi in xl)

            {

                XmlNodeList xcells = xi.SelectNodes("ss:Cell", nsmgr);

                Col = 0;

                foreach (XmlElement xcell in xcells)

                {

                    if (Row == -1)

                    {

                        dt.Columns.Add(xcell.InnerText);

                        cols[Col++] = xcell.InnerText;

                    }

                    else

                    {

                        if (xcell.Attributes["ss:Index"] != null)

                        {

                            int idx = int.Parse(xcell.Attributes["ss:Index"].InnerText);

                            Col = idx - 1;

                        }

                        SetCol(dt, Row, (string)cols[Col++], xcell.InnerText, typeof(string));

                    }

                }

                Row++;

            }

            return dt;

        }

        /// <summary>

        /// Adds row to datatable, manages System.DBNull and so

        /// </summary>

        /// <param name="dt"></param>

        /// <param name="AcceptChanges"></param>

        /// <returns></returns>

        public static int AddRow(DataTable dt, bool AcceptChanges)

        {

            object[] Values = new object[dt.Columns.Count];

            for (int Column = 0; Column < dt.Columns.Count; Column++)

            {

                if (!dt.Columns[Column].AllowDBNull)

                {

                    if (dt.Columns[Column].DefaultValue != null &&

                        dt.Columns[Column].DefaultValue != System.DBNull.Value)

                    {

                        Values[Column] = dt.Columns[Column].DefaultValue;

                    }

                }

            }

            dt.Rows.Add(Values);

            if (AcceptChanges)

            {

                dt.AcceptChanges();

            }

            return dt.Rows.Count - 1;

        }

        /// <summary>

        /// Sets data into datatable in safe manner of row index

        /// </summary>

        /// <param name="dt">DataTable to set</param>

        /// <param name="Row">Ordinal row index</param>

        /// <param name="ColumnName">name of column to set</param>

        /// <param name="Value">non/typed value to set</param>

        /// <param name="TypeOfValue">Becase Value can be null we must know datatype to manage default values</param>

        /// <returns></returns>

        public static DataColumn SetCol(DataTable dt, int Row, string ColumnName,

                                        object Value, System.Type TypeOfValue)

        {

            if (dt == null || ColumnName == null || ColumnName == "")

                return null;

            if (Value == null)

                Value = System.DBNull.Value;

            int nIndex = -1;

            DataColumn dcol = null;

            bool Added = false;

            if (dt.Columns.Contains(ColumnName))

            {

                dcol = dt.Columns[ColumnName];

            }

            else

            {

                dcol = dt.Columns.Add(ColumnName, TypeOfValue);

            }

            if (dcol.ReadOnly)

                dcol.ReadOnly = false;

            nIndex = dcol.Ordinal;

            //new empty row appended

            if (dt.Rows.Count == Row && Row >= 0)

            {

                AddRow(dt, false);

                Added = true;

            }

            //one row

            if (Row >= 0)

            {

                dt.Rows[Row][nIndex] = Value;

            }

            else if (Row == -1)

            { //all rows

                try

                {

                    for (Row = 0; Row < dt.Rows.Count; Row++)

                    {

                        if (dt.Rows[Row].RowState == DataRowState.Deleted)

                        {

                            continue;

                        }

                        dt.Rows[Row][nIndex] = Value;

                    }

                }

                catch (Exception)

                {

                }

            }

            return dcol;

        }

    }

}

Ref link:

http://www.codeproject.com/KB/dotnet/Excel_without_interop.aspx

Error - Application popup: wzunzip.exe while using WinZip 14.5 command line in Window service.

Bug :Was not able extract any zip files( WinZip 14.5 ) through Window service application ( which was using WinZip 14.5  command line )

Description: An Information found in Event logger – System as

Application popup: wzunzip.exe - Application Error : The application failed to initialize properly (0xc0000142). Click on OK to terminate the application.

Resolved :

This can be resolved just setting up the property of your application window service as below .

By check – Allow service to interact with desktop , it is said to service allow popup windows but by default service will run in hidden / silent mode . So nothing to worry about the popup .

Using Winzip14.5 command line – wzunzip.exe

Code snippet, I used in my application testing .

 using System;  
 using System.Collections.Generic;  
 using System.Text;  
 using System.IO;  
 namespace UnZipTest  
 {  
   public class UnZip  
   {  
     public void Run (string path,string extractPath){  
       DirectoryInfo dirInfo = new DirectoryInfo(path);  
       System.Diagnostics.Process P = new System.Diagnostics.Process();  
       if(Directory.Exists(extractPath)==false){  
         Directory.CreateDirectory(extractPath);  
       }  
       string UnZipCmd = null;  
       foreach(FileInfo fl in dirInfo.GetFiles("*.zip")){  
         try  
         {  
           string password = "123";  
           string zipExtract = extractPath + "\\" + fl.Name + "_" + Guid.NewGuid().ToString().Substring(0, 4);  
           if (password.Trim().Length == 0)  
           {  
             UnZipCmd = string.Format("-o \"{0}\" \"{1}\"", fl.FullName, zipExtract);  
             P = System.Diagnostics.Process.Start(@"C:\Program Files\WinZip\wzunzip", UnZipCmd);  
           }  
           else{  
             UnZipCmd = string.Format("-s\"{0}\" -o \"{1}\" \"{2}\"",password, fl.FullName, zipExtract);  
             P = System.Diagnostics.Process.Start(@"C:\Program Files\WinZip\wzunzip", UnZipCmd);  
           }  
           P.WaitForExit();  
         }  
         catch (Exception ex){  
           throw ex;  
         }  
       }  
     }  
   }  
 }  

Speed Test Details

 

 

http://testinternetspeed.org/

 

 

 

Kilobit per second

A kilobit per second (kbit/s or kb/s or kbps) is a unit of data transfer rate equal to:

• 1,000 bits per second or
• 125 bytes per second.

[edit] Megabit per second

A megabit per second (Mbit/s or Mb/s or Mbps; not to be confused with mbit/s which means, literally, millibit per second) is a unit of data transfer rate equal to:

• 1,000,000 bits per second or
• 1,000 kilobits per second or
• 125,000 bytes per second.

[edit] Gigabit per second

A gigabit per second (Gbit/s or Gb/s or Gbps) is a unit of data transfer rate equal to:

• 1,000 Megabits per second or
• 1,000,000 Kilobits per second or
• 1,000,000,000 Bits per second or
• 125,000,000 bytes per second

 

"upper" in case of schema.ini / oledb / excel sheet query

In case of querying with the Schema.ini / oledb / excel sheet query  and u need to apply a check based on upper case then you need to declare like this.

 

UCASE(RTRIM(LTRIM((C.NAME)))) = UCASE(RTRIM(LTRIM((A.NAME))))

 

For lower case use this-  LCASE.

 

 

 

 

Create or Delete A Service in Windows XP

Create or Delete A Service in Windows XP

Services are added from the Command Prompt. You need to know the actual service name as opposed to what Microsoft calls the Display Name. For example, if you wanted to create or delete the Help and Support service, the name used at the Command Prompt would be "helpsvc" rather than the Display Name of "Help and Support". The actual service name can be obtained by typing services.msc in Run on the Start Menu and then double clicking the Display Name of the service. Once you know the name;

To Create A Service

• Start | Run and type cmd in the Open: line. Click OK.
• Type: sc create <service name>
• Reboot the system

To Delete A Service

• Start | Run and type cmd in the Open: line. Click OK.
• Type: sc delete <service name>
• Reboot the system

---

If you prefer to work in the registry rather than through the command prompt to delete services;

• Click Start | Run and type regedit in the Open: line. Click OK.
• Navigate to HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services
• Scroll down the left pane, locate the service name, right click it and select Delete.
• Reboot the system

 

CAST and CONVERT (Transact-SQL)

CAST and CONVERT (Transact-SQL)

Converts an expression of one data type to another.

Transact-SQL Syntax Conventions

Syntax for CAST:

CAST (expression AS data_type [ (length ) ] )Syntax for CONVERT:

CONVERT (data_type  [ (length ) ] ,expression [ ,style ] )

 Arguments

expression

Is any valid expression.

data_type

Is the target data type. This includes xml, bigint, and sql_variant. Alias data types cannot be used. For more information about available data types, see Data Types (Transact-SQL).

length

Is an optional integer that specifies the length of the target data type. The default value is 30.

style

Is an integer expression that specifies how the CONVERT function is to translate expression. If style is NULL, NULL is returned. The range is determined by data_type. For more information, see the Remarks section.

 Return Types

Returns expression translated to data_type.

 Remarks

Date and Time Styles

When expression is a date or time data type, style can be one of the values shown in the following table. Other values are processed as 0. SQL Server supports the date format in Arabic style by using the Kuwaiti algorithm.

Without century (yy) (1)	With century (yyyy)	Standard	Input/Output (3)
-	0 or 100 (1,2)	Default	mon dd yyyy hh:miAM (or PM)
1	101	U.S.	mm/dd/yyyy
2	102	ANSI	yy.mm.dd
3	103	British/French	dd/mm/yyyy
4	104	German	dd.mm.yy
5	105	Italian	dd-mm-yy
6	106(1)	-	dd mon yy
7	107(1)	-	Mon dd, yy
8	108	-	hh:mi:ss
-	9 or 109 (1,2)	Default + milliseconds	mon dd yyyy hh:mi:ss:mmmAM (or PM)
10	110	USA	mm-dd-yy
11	111	JAPAN	yy/mm/dd
12	112	ISO	yymmdd yyyymmdd
-	13 or 113 (1,2)	Europe default + milliseconds	dd mon yyyy hh:mi:ss:mmm(24h)
14	114	-	hh:mi:ss:mmm(24h)
-	20 or 120 (2)	ODBC canonical	yyyy-mm-dd hh:mi:ss(24h)
-	21 or 121 (2)	ODBC canonical (with milliseconds)	yyyy-mm-dd hh:mi:ss.mmm(24h)
-	126 (4)	ISO8601	yyyy-mm-ddThh:mi:ss.mmm (no spaces)
-	127(6, 7)	ISO8601 with time zone Z.	yyyy-mm-ddThh:mi:ss.mmmZ (no spaces)
-	130 (1,2)	Hijri (5)	dd mon yyyy hh:mi:ss:mmmAM
-	131 (2)	Hijri (5)	dd/mm/yy hh:mi:ss:mmmAM

1 These style values return nondeterministic results. Includes all (yy) (without century) styles and a subset of (yyyy) (with century) styles.

2 The default values (style0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121) always return the century (yyyy).

3 Input when you convert to datetime; output when you convert to character data.

4 Designed for XML use. For conversion from datetime or smalldatetime to character data, the output format is as described in the previous table.

5 Hijri is a calendar system with several variations. SQL Server uses the Kuwaiti algorithm.

Important

By default, SQL Server interprets two-digit years based on a cutoff year of 2049. That is, the two-digit year 49 is interpreted as 2049 and the two-digit year 50 is interpreted as 1950. Many client applications, such as those based on Automation objects, use a cutoff year of 2030. SQL Server provides the two digit year cutoff configuration option that changes the cutoff year used by SQL Server and allows for the consistent treatment of dates. We recommend specifying four-digit years.

6 Only supported when casting from character data to datetime or smalldatetime. When character data that represents only date or only time components is cast to the datetime or smalldatetime data types, the unspecified time component is set to 00:00:00.000, and the unspecified date component is set to 1900-01-01.

7The optional time zone indicator, Z, is used to make it easier to map XML datetime values that have time zone information to SQL Server datetime values that have no time zone. Z is the indicator for time zone UTC-0. Other time zones are indicated with HH:MM offset in the + or - direction. For example: 2006-12-12T23:45:12-08:00.

When you convert to character data from smalldatetime, the styles that include seconds or milliseconds show zeros in these positions. You can truncate unwanted date parts when you convert from datetime or smalldatetime values by using an appropriate char or varchar data type length.

When you convert to datetimeoffset from character data with a style that includes a time, a time zone offset is appended to the result.

float and real Styles

When expression is float or real, style can be one of the values shown in the following table. Other values are processed as 0.

Value	Output
0 (default)	A maximum of 6 digits. Use in scientific notation, when appropriate.
1	Always 8 digits. Always use in scientific notation.
2	Always 16 digits. Always use in scientific notation.
126, 128, 129	Included for legacy reasons and might be deprecated in a future release.

money and smallmoney Styles

When expression is money or smallmoney, style can be one of the values shown in the following table. Other values are processed as 0.

Value	Output
0 (default)	No commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 4235.98.
1	Commas every three digits to the left of the decimal point, and two digits to the right of the decimal point; for example, 3,510.92.
2	No commas every three digits to the left of the decimal point, and four digits to the right of the decimal point; for example, 4235.9819.
126	Equivalent to style 2 when converting to char(n) or varchar(n)

xml Styles

When expression is xml, style can be one of the values shown in the following table. Other values are processed as 0.

Value	Output
0 (default)	Use default parsing behavior that discards insignificant white space and does not allow for an internal DTD subset. Note When you convert to the xml data type, SQL Server insignificant white space is handled differently than in XML 1.0. For more information, see Generating XML Instances.
1	Preserve insignificant white space. This style setting sets the default xml:space handling to behave the same as if xml:space="preserve" has been specified instead.
2	Enable limited internal DTD subset processing. If enabled, the server can use the following information that is provided in an internal DTD subset to perform nonvalidating parse operations. ·         Defaults for attributes are applied. ·         Internal entity references are resolved and expanded. ·         The DTD content model will be checked for syntactical correctness. The parser will ignore external DTD subsets. It also does not evaluate the XML declaration to see whether the standalone attribute is set yes or no, but instead parses the XML instance as if it is a stand-alone document.
3	Preserve insignificant white space and enable limited internal DTD subset processing.

Binary Styles

When expression is binary(n), varbinary(n), char(n), or varchar(n), style can be one of the values shown in the following table. Style values that are not listed in the table return an error.

Value	Output
0 (default)	Translates ASCII characters to binary bytes or binary bytes to ASCII characters. Each character or byte is converted 1:1. If the data_type is a binary type, the characters 0x are added to the left of the result.
1, 2	If the data_type is a binary type, the expression must be a character expression. The expression must be composed of an even number of hexadecimal digits (0, 1, 2, 3, 4, 5, 6, 7, 8, 9, A, B, C, D, E, F, a, b, c, d, e, f). If the style is set to 1 the characters 0x must be the first two characters in the expression. If the expression contains an odd number of characters or if any of the characters are invalid an error is raised. If the length of the converted expression is greater than the length of the data_type the result will be right truncated. Fixed length data_types that are larger then the converted result will have zeros added to the right of the result. If the data_type is a character type, the expression must be a binary expression. Each binary character is converted into two hexadecimal characters. If the length of the converted expression is greater than the data_type length it will be right truncated. If the data_type is a fix sized character type and the length of the converted result is less than its length of the data_type; spaces are added to the right of the converted expression to maintain an even number of hexadecimal digits. The characters 0x will be added to the left of the converted result for style 1.

Implicit Conversions

Implicit conversions are those conversions that occur without specifying either the CAST or CONVERT function. Explicit conversions are those conversions that require the CAST or CONVERT function to be specified. The following illustration shows all explicit and implicit data type conversions that are allowed for SQL Server system-supplied data types. These include xml, bigint, and sql_variant. There is no implicit conversion on assignment from the sql_variant data type, but there is implicit conversion to sql_variant.

When you convert between datetimeoffset and the character types char, varchar, nchar, and nvarchar the converted time zone offset part should always be double digits for both HH and MM for example, -08:00.

Note

Because Unicode data always uses an even number of bytes, use caution when you convert binary or varbinary to or from Unicode supported data types. For example, the following conversion does not return a hexadecimal value of 41; it returns 4100: SELECT CAST(CAST(0x41 AS nvarchar) AS varbinary).

Large-Value Data Types

Large-value data types exhibit the same implicit and explicit conversion behavior as their smaller counterparts, specifically the varchar, nvarchar and varbinary data types. However, you should consider the following guidelines:

·         Conversion from image to varbinary(max) and vice-versa is an implicit conversion, and so are conversions between text and varchar(max), and ntext and nvarchar(max).

·         Conversion from large-value data types, such as varchar(max), to a smaller counterpart data type, such as varchar, is an implicit conversion, but truncation will occur if the large value is too big for the specified length of the smaller data type.

·         Conversion from varchar, nvarchar, or varbinary to their corresponding large-value data types is performed implicitly.

·         Conversion from the sql_variant data type to the large-value data types is an explicit conversion.

·         Large-value data types cannot be converted to the sql_variant data type.

For information about how to convert Microsoft .NET Framework common language runtime (CLR) user-defined types, see Performing Operations on User-defined Types. For more information about how to convert from the xml data type, see Generating XML Instances.

xml Data Type

When you explicitly or implicitly cast the xml data type to a string or binary data type, the content of the xml data type is serialized based on a set of rules. For information about these rules, see Serialization of XML Data. For information on how to cast from XML to a CLR user-defined type, see Performing Operations on User-defined Types. For information about how to convert from other data types to the xml data type, see Generating XML Instances.

text and image Data Types

Automatic data type conversion is not supported for the text and image data types. You can explicitly convert text data to character data, and image data to binary or varbinary, but the maximum length is 8000 bytes. If you try an incorrect conversion such as trying to convert a character expression that includes letters to an int, SQL Server returns an error message.

Output Collation

When the output of CAST or CONVERT is a character string, and the input is a character string, the output has the same collation and collation label as the input. If the input is not a character string, the output has the default collation of the database, and a collation label of coercible-default. For more information, see Collation Precedence (Transact-SQL).

To assign a different collation to the output, apply the COLLATE clause to the result expression of the CAST or CONVERT function. For example:

SELECT CAST('abc' AS varchar(5)) COLLATE French_CS_AS

Truncating and Rounding Results

When you convert character or binary expressions (char, nchar, nvarchar, varchar, binary, or varbinary) to an expression of a different data type, data can be truncated, only partially displayed, or an error is returned because the result is too short to display. Conversions to char, varchar, nchar, nvarchar, binary, and varbinary are truncated, except for the conversions shown in the following table.

From data type	To data type	Result
int, smallint, or tinyint	char	*
	varchar	*
	nchar	E
	nvarchar	E
money, smallmoney, numeric, decimal, float, or real	char	E
	varchar	E
	nchar	E
	nvarchar	E

* = Result length too short to display. E = Error returned because result length is too short to display.

SQL Server guarantees that only roundtrip conversions, conversions that convert a data type from its original data type and back again, will yield the same values from version to version. The following example shows such a roundtrip conversion:

Copy Code

DECLARE @myval decimal (5, 2)

SET @myval = 193.57

SELECT CAST(CAST(@myval AS varbinary(20)) AS decimal(10,5))

-- Or, using CONVERT

SELECT CONVERT(decimal(10,5), CONVERT(varbinary(20), @myval))

Note

Do not try to construct binary values and then convert them to a data type of the numeric data type category. SQL Server does not guarantee that the result of a decimal or numeric data type conversion to binary will be the same between versions of SQL Server.

The following example shows a resulting expression that is too small to display.

Copy Code

USE AdventureWorks2008R2;

GO

SELECT p.FirstName, p.LastName, SUBSTRING(p.Title, 1, 25) AS Title, CAST(e.SickLeaveHours AS char(1)) AS 'Sick Leave'

FROM HumanResources.Employee e JOIN Person.Person p ON e.BusinessEntityID = p.BusinessEntityID

WHERE NOT e.BusinessEntityID >5;

Here is the result set.

FirstName LastName Title Sick Leave

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

Ken Sanchez NULL *

Terri Duffy NULL *

Roberto Tamburello NULL *

Rob Walters NULL *

Gail Erickson Ms. *

(5 row(s) affected)

When you convert data types that differ in decimal places, sometimes the result value is truncated and at other times it is rounded. The following table shows the behavior.

From	To	Behavior
numeric	numeric	Round
numeric	int	Truncate
numeric	money	Round
money	int	Round
money	numeric	Round
float	int	Truncate
float	numeric	Round
float	datetime	Round
datetime	int	Round

For example, the result of the following conversion is 10:

SELECT CAST(10.6496 AS int)

When you convert data types in which the target data type has fewer decimal places than the source data type, the value is rounded. For example, the result of the following conversion is $10.3497:

SELECT CAST(10.3496847 AS money)

SQL Server returns an error message when nonnumeric char, nchar, varchar, or nvarchar data is converted to int, float, numeric, or decimal. SQL Server also returns an error when an empty string (" ") is converted to numeric or decimal.

Certain datetime Conversions Are Nondeterministic in SQL Server 2005 and Later Versions

In SQL Server 2000, string to date and time conversions are marked as deterministic. However, this is not true for the styles listed in the following table. For these styles, the conversions depend on the language settings. SQL Server 2005 and later versions mark these conversions as nondeterministic.

The following table lists the styles for which the string-to-datetime conversion is nondeterministic.

All styles below 1001	106
107	109
113	130

1 With the exception of styles 20 and 21

 Examples

A. Using both CAST and CONVERT

Each example retrieves the name of the product for those products that have a 3 in the first digit of their list price and converts their ListPrice to int.

Copy Code

-- Use CAST

USE AdventureWorks2008R2;

GO

SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice

FROM Production.Product

WHERE CAST(ListPrice AS int) LIKE '3%';

GO

-- Use CONVERT.

USE AdventureWorks2008R2;

GO

SELECT SUBSTRING(Name, 1, 30) AS ProductName, ListPrice

FROM Production.Product

WHERE CONVERT(int,  ListPrice) LIKE '3%';

GO

B. Using CAST with arithmetic operators

The following example calculates a single column computation (Computed) by dividing the total year-to-date sales (SalesYTD) by the commission percentage (CommissionPCT). This result is converted to an int data type after being rounded to the nearest whole number.

Copy Code

USE AdventureWorks2008R2;

GO

SELECT CAST(ROUND(SalesYTD/CommissionPCT, 0) AS int) AS 'Computed'

FROM Sales.SalesPerson 

WHERE CommissionPCT != 0;

GO

Here is the result set.

Computed

------

379753754

346698349

257144242

176493899

281101272

0

301872549

212623750

298948202

250784119

239246890

101664220

124511336

97688107

(14 row(s) affected)

C. Using CAST to concatenate

The following example concatenates noncharacter, nonbinary expressions by using CAST.

Copy Code

USE AdventureWorks2008R2;

GO

SELECT 'The list price is ' + CAST(ListPrice AS varchar(12)) AS ListPrice

FROM Production.Product

WHERE ListPrice BETWEEN 350.00 AND 400.00;

GO

Here is the result set.

ListPrice

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

The list price is 357.06

The list price is 364.09

The list price is 364.09

The list price is 364.09

The list price is 364.09

(5 row(s) affected)

D. Using CAST to produce more readable text

The following example uses CAST in the select list to convert the Name column to a char(10) column.

Copy Code

USE AdventureWorks2008R2;

GO

SELECT DISTINCT CAST(p.Name AS char(10)) AS Name, s.UnitPrice

FROM Sales.SalesOrderDetail s JOIN Production.Product p on s.ProductID = p.ProductID

WHERE Name LIKE 'Long-Sleeve Logo Jersey, M';

GO

Here is the result set.

Name UnitPrice

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

Long-Sleev 31.2437

Long-Sleev 32.4935

Long-Sleev 49.99

(3 row(s) affected)

E. Using CAST with the LIKE clause

The following example converts the money column SalesYTD to an int and then to a char(20) column so that it can be used with the LIKE clause.

Copy Code

USE AdventureWorks2008R2;

GO

SELECT p.FirstName, p.LastName, s.SalesYTD, s.BusinessEntityID

FROM Person.Person  p JOIN Sales.SalesPerson s ON p.BusinessEntityID = s.BusinessEntityID

WHERE CAST(CAST(s.SalesYTD AS int) AS char(20)) LIKE '2%';

GO

Here is the result set.

FirstName LastName SalesYTD SalesPersonID

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

Tsvi Reiter 2811012.7151 279

Syed Abbas 219088.8836 288

Rachel Valdez 2241204.0424 289

(3 row(s) affected)

F. Using CONVERT or CAST with typed XML

The following are several examples that show using CONVERT to convert to typed XML by using the xml data type.

This example converts a string with white space, text and markup into typed XML and removes all insignificant white space (boundary white space between nodes):

Copy Code

CONVERT(XML, '<root><child/></root>')

This example converts a similar string with white space, text and markup into typed XML and preserves insignificant white space (boundary white space between nodes):

Copy Code

CONVERT(XML, '<root>          <child/>         </root>', 1)

This example casts a string with white space, text, and markup into typed XML:

Copy Code

CAST('<Name><FName>Carol</FName><LName>Elliot</LName></Name>'  AS XML)

For more examples, see Generating XML Instances.

G. Using CAST and CONVERT with datetime data

The following example displays the current date and time, uses CAST to change the current date and time to a character data type, and then uses CONVERT display the date and time in the ISO 8901 format.

Copy Code

SELECT 

   GETDATE() AS UnconvertedDateTime,

   CAST(GETDATE() AS nvarchar(30)) AS UsingCast,

   CONVERT(nvarchar(30), GETDATE(), 126) AS UsingConvertTo_ISO8601  ;

GO

Here is the result set.

UnconvertedDateTime     UsingCast                      UsingConvertTo_ISO8601

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

2006-04-18 09:58:04.570 Apr 18 2006 9:58AM            2006-04-18T09:58:04.570

(1 row(s) affected)

The following example is approximately the opposite of the previous example. The example displays a date and time as character data, uses CAST to change the character data to the datetime data type, and then uses CONVERT to change the character data to the datetime data type.

Copy Code

SELECT 

   '2006-04-25T15:50:59.997' AS UnconvertedText,

   CAST('2006-04-25T15:50:59.997' AS datetime) AS UsingCast,

   CONVERT(datetime, '2006-04-25T15:50:59.997', 126) AS UsingConvertFrom_ISO8601 ;

GO

Here is the result set.

UnconvertedText         UsingCast               UsingConvertFrom_ISO8601

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

2006-04-25T15:50:59.997 2006-04-25 15:50:59.997 2006-04-25 15:50:59.997

(1 row(s) affected)

H. Using CONVERT with binary and character data

The following examples show the results of converting binary and character data by using different styles.

Copy Code

--Convert the binary value 0x4E616d65 to a character value.

SELECT CONVERT(char(8), 0x4E616d65, 0) AS 'Style 0, binary to character'

Here is the result set.

Style 0, binary to character

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

Name

(1 row(s) affected)

Copy Code

--The following example shows how Style 1 can force the result

--to be truncated.  The truncation is caused by

--including the characters 0x in the result.

SELECT CONVERT(char(8), 0x4E616d65, 1) AS 'Style 1, binary to character'

Here is the result set.

Style 1, binary to character

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

0x4E616D

(1 row(s) affected)

Copy Code

--The following example shows that Style 2 does not truncate the

--result because the characters 0x are not included in

--the result.

SELECT CONVERT(char(8), 0x4E616d65, 2) AS 'Style 2, binary to character'

Here is the result set.

Style 2, binary to character

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

4E616D65

(1 row(s) affected)

Copy Code

--Convert the character value 'Name' to a binary value.

SELECT CONVERT(binary(8), 'Name', 0) AS 'Style 0, character to binary'

Here is the result set.

Style 0, character to binary

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

0x4E616D6500000000

(1 row(s) affected)

Copy Code

SELECT CONVERT(binary(4), '0x4E616D65', 1) AS 'Style 1, character to binary'

Here is the result set.

Style 1, character to binary

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

0x4E616D65

(1 row(s) affected)

Copy Code

SELECT CONVERT(binary(4), '4E616D65', 2) AS 'Style 2,  character to binary'

Here is the result set.

Style 2, character to binary

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

0x4E616D65

(1 row(s) affected)

 See Also

Other Resources

Data Type Conversion (Database Engine)

SELECT (Transact-SQL)

System Functions (Transact-SQL)

Writing International Transact-SQL Statements