Datatypes | SQL with Manoj

How to convert ‘20140620’ date to ‘DD-MM-YYYY’ format – MSDN TSQL forum

June 12, 2014 Leave a comment

–> Question:

I have data for date fields like “20140620”(YYYYMMDD) while inserting into the table it should be in the format “DD-MM-YYYY”

Can any one please help me to achive this.

–> My Response:

SELECT FORMAT(CAST('20140620' as DATE), 'dd-MM-yyyy', 'en-US'), CAST('20140620' as DATE)

… this will work with SQL Server 2012 and above.

–> Other Responses:

-- by Olaf Helper
SELECT CONVERT(varchar(10), CONVERT(datetime, '20140620', 112), 105) AS DDMMYYY

-- by Latheesh NK
SELECT CONVERT(VARCHAR(10), '20140620', 105)

Ref Link.

Categories: Datatypes, SQL Server Questions Tags: Convert Date, MSDN TSQL forum

Difference between Decimal and Numeric datatypes

June 8, 2012 4 comments

Decimal vs Numeric: None… both are same!!!

This is a misconception among many developers that Decimal and Numeric are different data types. And lot of people do not agree to understand that they are same unless I redirect them to this MSDN BOL [link].

Few days back while designing a data model for one our support enhancement project this question was raised and following was the conversation:

Person A: Ok, this column is defined as Numeric, can’t we make it a Decimal?
Me: What difference will it make?
Person A: Decimal is more precise than Numeric (according to him).
Me: No, both are same.
Person B, C and some others: There is slight difference, Decimal holds more accurate precision values than Numeric.

To my surprise none of them were aware that both are same.

So, when I redirected them to MSDN decimal and numeric (Transact-SQL) and show them the line which says: “numeric is functionally equivalent to decimal.” they agreed.

So, not to confuse between Decimal & Numeric, they both are synonyms to each other.

Categories: Datatypes, Differences, Misconception Tags: Decimal, Decimal vs Numeric, Numeric

MSDN & Online Technical Articles on using XML with SQL

December 22, 2011 Leave a comment

On this post I’m not going to discuss anything about XML. Here I’m listing out some links that I found very informative so that I can refer to them easily in future, and so can you.

My all previous posts on XML-SQL can be found at: https://sqlwithmanoj.wordpress.com/category/xml/

There are also lot of Technical Articles in MS BOL that discuss about using XML in SQL Server.

Here is a list of those:

What’s New in FOR XML: http://msdn.microsoft.com/en-US/library/ms345137(v=SQL.90).aspx

XML Best Practices: http://msdn.microsoft.com/en-US/library/ms345115(v=SQL.90).aspx

XML Indexes: http://msdn.microsoft.com/en-US/library/ms345121(v=SQL.90).aspx

XML Options: http://msdn.microsoft.com/en-US/library/ms345110(v=SQL.90).aspx

XML Support: http://msdn.microsoft.com/en-US/library/ms345117(v=SQL.90).aspx

Performance Optimizations for the XML Data Type: http://msdn.microsoft.com/en-US/library/ms345118(v=SQL.90).aspx

… enjoy reading.

Please post your comments and links if you found any informative online article about XML-SQL.

Categories: Datatypes, XML Tags: XML, XML Best Practices, XML index, XML SQL

Using SQL_VARIANT datatype to store any datatype value in SQL Server

January 27, 2011 Leave a comment

According to MS BOL SQL_VARIANT can be used in columns, parameters, variables, and the return values of user-defined functions. sql_variant enables these database objects to support values of other data types.

It can have a maximum length of 8016 bytes including both the base-type information and the base-type value. Thus the maximum length of the actual base-type value is 8,000 bytes.

USE [tempdb]
GO

DECLARE @var SQL_VARIANT

-- Set variable as DATETIME type
SET @var =GETDATE()
SELECT @var as Data, SQL_VARIANT_PROPERTY(@var , 'BaseType') as BaseType
, SQL_VARIANT_PROPERTY(@var , 'Precision') as Precision
, SQL_VARIANT_PROPERTY(@var , 'Scale') as Scale
, SQL_VARIANT_PROPERTY(@var , 'TotalBytes') as TotalBytes
, SQL_VARIANT_PROPERTY(@var , 'MaxLength') as MaxLength
, SQL_VARIANT_PROPERTY(@var , 'Collation') as Collation;

Output:
Data            	BaseType Precision Scale TotalBytes MaxLength Collation
2011-01-27 17:13:53.150	datetime 23        3     10         8         NULL

-- Set variable as INT type
SET @var = 1234
SELECT @var as Data, SQL_VARIANT_PROPERTY(@var , 'BaseType') as BaseType
, SQL_VARIANT_PROPERTY(@var , 'Precision') as Precision
, SQL_VARIANT_PROPERTY(@var , 'Scale') as Scale
, SQL_VARIANT_PROPERTY(@var , 'TotalBytes') as TotalBytes
, SQL_VARIANT_PROPERTY(@var , 'MaxLength') as MaxLength
, SQL_VARIANT_PROPERTY(@var , 'Collation') as Collation;

Output:
Data	BaseType Precision Scale TotalBytes MaxLength Collation
1234	int      10        0     6          4         NULL

-- Set variable as VARCHAR type
SET @var = SYSTEM_USER
SELECT @var as Data, SQL_VARIANT_PROPERTY(@var , 'BaseType') as BaseType
, SQL_VARIANT_PROPERTY(@var , 'Precision') as Precision
, SQL_VARIANT_PROPERTY(@var , 'Scale') as Scale
, SQL_VARIANT_PROPERTY(@var , 'TotalBytes') as TotalBytes
, SQL_VARIANT_PROPERTY(@var , 'MaxLength') as MaxLength
, SQL_VARIANT_PROPERTY(@var , 'Collation') as Collation;

Output:
Data        BaseType Precision Scale TotalBytes MaxLength Collation
XXX\mpandey nvarchar 0         0     30         256       Latin1_General_CS_AS

-- Set variable as BIT type
SET @var = cast(1 as bit)
SELECT @var as Data, SQL_VARIANT_PROPERTY(@var , 'BaseType') as BaseType
, SQL_VARIANT_PROPERTY(@var , 'Precision') as Precision
, SQL_VARIANT_PROPERTY(@var , 'Scale') as Scale
, SQL_VARIANT_PROPERTY(@var , 'TotalBytes') as TotalBytes
, SQL_VARIANT_PROPERTY(@var , 'MaxLength') as MaxLength
, SQL_VARIANT_PROPERTY(@var , 'Collation') as Collation;

Output:
Data	BaseType Precision Scale TotalBytes MaxLength Collation
1	bit      1         0     3          1         NULL

More on SQL_VARIANT: http://msdn.microsoft.com/en-us/library/ms173829.aspx

Categories: Datatypes Tags: SQL_VARIANT, SQL_VARIANT_PROPERTY

SQL Server CURSOR and it’s Life Cycle

October 24, 2010 1 comment

A CURSOR in SQL is a database object that contains a set of records that you can traverse one-by-one, rather than the SET as a whole.

SQL is a set-based language and produces a complete result set, and the SQL queries works on this complete set only, and not on individual rows. But there are times when the results are best processed one row at a time. Opening a cursor on a result set allows processing the result set one row at a time. You can assign a cursor to a variable or parameter with a cursor data type.

CURSOR in SQL language gives you the flexibility to traverse records like the way you do in other programming languages with iterators and for-loop.

–> A simple Cursor life cycle with minimum definition:

USE [AdventureWorks]
GO

-- Returns -3
SELECT CURSOR_STATUS('global','myCursor') AS 'Before Declare'

DECLARE myCursor CURSOR
FOR SELECT ContactID, FirstName, LastName FROM Person.Contact WHERE ContactID BETWEEN 100 and 102

-- Returns -1
SELECT CURSOR_STATUS('global','myCursor') AS 'After Declare'

DECLARE @ContactID INT, @FirstName VARCHAR(50), @LastName VARCHAR(50)

OPEN  myCursor

-- Returns 1
SELECT CURSOR_STATUS('global','myCursor') AS 'Open Cusrsor'

FETCH NEXT FROM myCursor INTO @ContactID, @FirstName, @LastName
WHILE @@fetch_status=0
BEGIN

	-- SQL Statements with logic inside
	SELECT @ContactID, @FirstName, @LastName

	FETCH NEXT FROM myCursor INTO @ContactID, @FirstName, @LastName
END

-- Returns 1
SELECT CURSOR_STATUS('global','myCursor') AS 'While loop exited, all rows iterated'

CLOSE myCursor

-- Returns -1
SELECT CURSOR_STATUS('global','myCursor') AS 'Cursor is Closed'

DEALLOCATE myCursor

-- Returns -3
SELECT CURSOR_STATUS('global','myCursor') AS 'Cursor Deallocated'

Do check & Like my FB Page.