Get the date diference in the form of x Years y months z days

Somethimes we need to get the difference of two dates in SQL server to send the result to front end. The result of DateDiff SQL function will give the difference of the date for a particular part like year or month or day... If you think of just concatinating the results got from three datediff function with parameters for year,month and day, you will get the wrong result. WHY???
DateDiff(Year,'2007/31/12','2008/1/1') is 1 though the difference only 1 day.
Similarly month differnce and day difference will be shown as 1.
So the result age is "1 year 1 month 1 day". But actyually only "1 day".
Here is a function i wrote to get the exact value. This is very useful when you need to get the age on today when you have DOB stored in your database. Here you have to send the DOB as @startdate and GetDate() as @endDate

Create FUNCTION [dbo].[fn_GetDateDifference] (@startDate as DateTime,@endDate DateTime)
RETURNS varchar(50) AS
BEGIN
DECLARE @res varchar(50)
DECLARE @mon int,@year int,@day int
SELECT @year = DateDiff(year,@startDate,@endDate)
--SET @year = @day/365
SET @startDate = DateAdd(Year,@Year,@startDate)
if(@startDate > @endDate) --If the month value of Start Date is more than that of End Date
--@startDate can become more than date 2 if the values are like
--@startDate = '2007/12/15' and @endDate = '2008/02/01',
--The datediff year will be 1 though only 2 month+ difference
BEGIN
SET @year = @year - 1
SET @startDate = DateAdd(Year,-1,@startDate)
END
SELECT @mon = DateDiff(Month,@startDate,@endDate)
--SET @mon = @day/28
--Select @mon = DateDiff(Month,@startDate,@endDate)
SET @startDate = DateAdd(Month,@mon,@startDate)
if(@startDate > @endDate)
--Same as year,month can show maximum of 1 unit more if the start date "Day" val in more than end date "Day value"
BEGIN
set @startDate = DateAdd(Month,-1,@startDate)
set @mon = @mon - 1
END
SELECT @day = DateDiff(Day,@startDate,@endDate)
set @res = ''
If(@year > 0)
SELECT @res = Cast(@year as Varchar(10)) + ' Year(s) '
if( @year>0 or @mon>0)
Set @res = @res + Cast(@mon as Varchar(10)) + ' Month(s) '

set @res = @res + Cast(@day as Varchar(10)) + ' Day(s)'
--set @res = @year & ' '
--select @res = @year & ' ' & @mon & ' ' &@day
RETURN @res
END

Note: It is not a good practice to use backend sqlserver for this kind of calculations. It is preferable to return two dates to front end and then do the desired calculations.

XML Encoding Problem In SQL Server

In a project i was sending an XML file to a SP which was written by someone. It was insering values to a master table and some details table. The SP was taking some TEXT datatype as input for the XML. Now the problem was one day we found some
"XML parsing error: An invalid character was found in text content. sql server xml parse" error and i come to know that some datatype is having some non-ascii characters like "Ö","«" for which the error was generated.
We were told to just avoid throughing error so that the user can continue their work. The fastest solution i came accross was to format the string before puttin into the XML.
So i wrote a function which will take the String as input and return the ASCII string as output as below.
public static string getUTF8String(string str) {
char[] ch = str.ToCharArray();
byte[] by = new byte[ch.Length * 2]; //If all char is UNicode then max byte is 2 X length System.Text.ASCIIEncoding enc = new ASCIIEncoding();
int charUsed, byteUsed;
bool completed;
enc.GetEncoder().Convert(ch, 0, ch.Length, by, 0, by.Length, true, out charUsed, out byteUsed, out completed);
string res=enc.GetString(by, 0, byteUsed);
return res;
}
So the Non-Ascii character may change, but the workflow wsa not hampered.
But the best solution is to change the SP and the xml sent to the SP. In the SP you should make the input parameter as NTEXT rather than TEXT and in the XML mention the encoding as UTF-16 - not UTF-8. In this way it will accept all unicode characters.

Using text, ntext Data Type SQL Server

In sql server 2000 (or before ver) we uses Text,NText data type for storing very long length of string datatype. Text is suitable for non-unicode (1 byte) string and NText for unicode(2 bytes). we can't manipulate these datatype with the regular dmls, but it requires some other way because of it's storing process which is different from other data types (except Image which is stored in the same way for binary data). These datatypes's values are stored outside the row and a 16 bit pointer is tored which points to the root of the internal pointer which points the fragment of the data stored in different pages.
We need to use READTEXT (to read), WRITETEXT ( to replace ) and UPDATETEXT (to modify) these values.
First create a table and insert some values in it
CREATE TABLE TestNTextDataType ( [Id] [int] NULL , [Name] [Ntext] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
insert into TestNTextDataType values(1,'This is a test')
insert into TestNTextDataType values(2,'This is second test')
Declare a pointer to the 'Name' Column as
Declare @ptrToCol varbinary(16)
Select @ptrToCol=TextPtr([Name]) from TestNTextDataType where [Id]=1
The @ptrToCol pointer points to the NText value 'This is a test'.

READTEXT { table.columnName @ptrToCol @offset @size } [ HOLDLOCK ]
Reads values from a column, starting from a specified offset and reading the specified number of bytes.
READTEXT TestNTextDataType.[Name] @ptrToCol 5 2
Result: is
The value of offset is 5 and as it is zero based it points to the 6th character 'i' and the length is 2 so the two characters starting from 'i' is returned. If you pass 0 as the size then 4KB of data is read.
Read More

WRITETEXT { table.columnName @ptrToCol } [ WITH LOG ] { data }
Permits minimally logged, interactive updating of an existing column. WRITETEXT overwrites any existing data in the column it affects
WRITETEXT TestNTextDataType.[Name] @ptrToCol 'Updated Test Final'
It will update the 'This is a test' value to the new value 'Updated Test Final'. Remember that the pointer @ptrToCol points to the [Name] column of the row with the id=1 (Where clause).
Read More

UPDATETEXTUPDATETEXT { table.columnName @ptrToCol } { NULL @insertOffset } { NULL @deleteLength } [ WITH LOG ] [ inserted_data]
Updates an existing field. Use UPDATETEXT to change only a part of a text, ntext, or image column in place.
UPDATETEXT TestNTextDataType.[Name] @ptrToCol 8 4 'New String'
Previous value was 'Updated Test Final' insert offset 8 points to 'T' of Test and the delete length 4 deletes the 'Test' and inserts the value 'New String'. So the result value becomes 'Updated New String Final'.
Read More

DataLength(Expression)
To get the length of the column value for Text,NText and Image datatype you have to use DataLength function.
Select DataLength([Name]) Length from TestNTextDataType where [Id]=1
will return 48 as the length of 'Updated New String Final' string because the datatype is NText of the column so every charter will take 2 byte and there are 24 characters. If the datatype was Text then it will return 24. The DATALENGTH of NULL is NULL.

PATINDEX , SET TEXTSIZE, SUBSTRING, TEXTVALID are use useful functions to work with these datatypes.
Note: ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types.