Get JSON string from datatable

        public static string GetJSONString(DataTable Dt)

        {

            string[] StrDc = new string[Dt.Columns.Count];

            string HeadStr = string.Empty;

            for (int i = 0; i < Dt.Columns.Count; i++)

            {

                StrDc[i] = Dt.Columns[i].Caption;

                HeadStr += "\"" + StrDc[i] + "\" : \"" + StrDc[i] + i.ToString() + "¾" + "\",";

            }

            HeadStr = HeadStr.Substring(0, HeadStr.Length - 1);

            StringBuilder Sb = new StringBuilder();

            Sb.Append("{\"" + Dt.TableName + "\" : [");

            for (int i = 0; i < Dt.Rows.Count; i++)

            {

                string TempStr = HeadStr;

                Sb.Append("{");

                for (int j = 0; j < Dt.Columns.Count; j++)

                {

                    TempStr = TempStr.Replace(Dt.Columns[j] + j.ToString() + "¾", Dt.Rows[i][j].ToString());

                }

                Sb.Append(TempStr + "},");

            }

            Sb = new StringBuilder(Sb.ToString().Substring(0, Sb.ToString().Length - 1));

            Sb.Append("]}");

            return Sb.ToString();

        }

Calculate timeago from Current time in SQL

Following function calculates timeago from current time.
For ex.
2 hours 5 minutes ago
23 sec ago etc

CREATE FUNCTION fn_relativeTime (@dateFrom DATETIME)
RETURNS VARCHAR(100)
AS
BEGIN
       DECLARE @ret VARCHAR(100)
       DECLARE @dateTo DATETIME
       DECLARE @tmpdate DATETIME
       DECLARE @years INT
       DECLARE @months INT
       DECLARE @days INT
       DECLARE @hours INT
       DECLARE @minutes INT
       DECLARE @seconds INT
       DECLARE @milliseconds INT

       SELECT @dateTo = getdate()

       SELECT @tmpdate = @dateFrom

       SELECT @years = DATEDIFF(yy, @tmpdate, @dateTo) - CASE WHEN (MONTH(@dateFrom) > MONTH(@dateTo)) OR (MONTH(@dateFrom) = MONTH(@dateTo) AND DAY(@dateFrom) > DAY(@dateTo)
                                  ) THEN 1 ELSE 0 END

       SELECT @tmpdate = DATEADD(yy, @years, @tmpdate)

       SELECT @months = DATEDIFF(mm, @tmpdate, @dateTo) - CASE WHEN DAY(@dateFrom) > DAY(@dateTo) THEN 1 ELSE 0 END

       SELECT @tmpdate = DATEADD(mm, @months, @tmpdate)

       SELECT @days = DATEDIFF(dd, @tmpdate, @dateTo)

       SELECT @tmpdate = DATEADD(dd, @days, @tmpdate)

       SELECT @hours = DATEDIFF(hh, @tmpdate, @dateTo)

       SELECT @tmpdate = DATEADD(hh, @hours, @tmpdate)

       SELECT @minutes = DATEDIFF(mi, @tmpdate, @dateTo)

       SELECT @tmpdate = DATEADD(mi, @minutes, @tmpdate)

       SELECT @seconds = DATEDIFF(ss, @tmpdate, @dateTo)

       SELECT @tmpdate = DATEADD(ss, @seconds, @tmpdate)

       SELECT @milliseconds = DATEDIFF(ms, @tmpdate, @dateTo)

       IF (@years != '0')
              SELECT @ret = cast(@years AS VARCHAR(10)) + ' years ' + (CASE WHEN cast(abs(@months) AS VARCHAR(10)) != '0' THEN cast(abs(@months) AS VARCHAR(10)) + ' months ' ELSE '' END
                           ) + (CASE WHEN cast(abs(@days) AS VARCHAR(10)) != '0' THEN cast(abs(@days) AS VARCHAR(10)) + ' days ' ELSE '' END)
       ELSE
              IF (@months != '0')
                     SELECT @ret = cast(@months AS VARCHAR(10)) + ' months ' + (CASE WHEN cast(abs(@days) AS VARCHAR(10)) != '0' THEN cast(abs(@days) AS VARCHAR(10)) + ' days' ELSE '' END
                                  )
              ELSE
                     IF (@days != '0')
                           SELECT @ret = cast(@days AS VARCHAR(10)) + ' days ' + (CASE WHEN cast(abs(@hours) AS VARCHAR(10)) != '0' THEN cast(abs(@hours) AS VARCHAR(10)) + ' hours' ELSE '' END
                                         )
                     ELSE
                           IF (@hours != '0')
                                  SELECT @ret = cast(@hours AS VARCHAR(10)) + ' hours ' + (CASE WHEN cast(abs(@minutes) AS VARCHAR(10)) != '0' THEN cast(abs(@minutes) AS VARCHAR(10)) + ' minutes' ELSE '' END
                                                )
                           ELSE
                                  IF (@minutes != '0')
                                         SELECT @ret = cast(@minutes AS VARCHAR(10)) + ' minutes ' + (CASE WHEN cast(abs(@seconds) AS VARCHAR(10)) != '0' THEN cast(abs(@seconds) AS VARCHAR(10)) + ' seconds' ELSE '' END
                                                       )

       RETURN @ret
END

Input:

select dbo.fn_relativeTime('2015-03-15')
select dbo.fn_relativeTime('2015-03-15 11:30:25')
select dbo.fn_relativeTime('2015-03-15 11:00')
select dbo.fn_relativeTime('2015-01-15')
select dbo.fn_relativeTime('2014-03-15')
select dbo.fn_relativeTime('2014-01-15')

Output:

Calculate Age from Date of Birth in SQL

Below SQL function is used to get age of a person from Date of Birth.

CREATE FUNCTION [dbo].[fn_GetAge] (@in_DOB AS DATETIME)
RETURNS INT
AS
BEGIN
 DECLARE @age INT
 DECLARE @now DATETIME = (
   SELECT GETDATE()
   )

 --PRINT @NOW              
 IF cast(datepart(m, @now) AS INT) > cast(datepart(m, @in_DOB) AS INT)
  SET @age = cast(datediff(yyyy, @in_DOB, @now) AS INT)
 ELSE IF cast(datepart(m, @now) AS INT) = cast(datepart(m, @in_DOB) AS INT)
  IF datepart(d, @now) >= datepart(d, @in_DOB)
   SET @age = cast(datediff(yyyy, @in_DOB, @now) AS INT)
  ELSE
   SET @age = cast(datediff(yyyy, @in_DOB, @now) AS INT) - 1
 ELSE
  SET @age = cast(datediff(yyyy, @in_DOB, @now) AS INT) - 1

 RETURN @age
END

Executing below SQL query you will get following output..

select dbo.fn_GetAge('1987-02-28'), GETDATE()
select dbo.fn_GetAge('1987-03-01'), GETDATE()
select dbo.fn_GetAge('1987-03-02'), GETDATE()

Get Age

SQL UDF to Remove Special Characters from Text

CREATE FUNCTION [dbo].[fn_RemoveSpecialCharacters] (@name VARCHAR(max))

RETURNS NVARCHAR(max)

AS

BEGIN

       DECLARE @str VARCHAR(MAx)

       SET @str = @name

       WHILE PATINDEX('%[ !@#$%^&*()<>?:"{},./;=+|\_]%', @str) > 0

              SET @str = REPLACE(@str, SUBSTRING(@str, PATINDEX('%[ !@#$%^&*()<>?:"{},./;=+|\_]%', @str), 1), '')

       RETURN replace(@str, '-', '')

END

SQL UDF to get float value from Text

CREATE FUNCTION [dbo].[fn_GetFloatFromText] (@name VARCHAR(max))

RETURNS NVARCHAR(max)

AS

BEGIN

       DECLARE @str VARCHAR(MAx)

       SET @str = @name

       WHILE PATINDEX('%[^0-9.]%', @str) > 0

              SET @str = REPLACE(@str, SUBSTRING(@str, PATINDEX('%[^0-9.]%', @str), 1), '')

       RETURN replace(@str, '-', '')

END

SQL UDF to get all integers from Text

CREATE FUNCTION [dbo].[fn_GetIntFromText] (@name VARCHAR(max))

RETURNS NVARCHAR(max)

AS

BEGIN

       DECLARE @str VARCHAR(MAx)

       SET @str = @name

       WHILE PATINDEX('%[^0-9]%', @str) > 0

              SET @str = REPLACE(@str, SUBSTRING(@str, PATINDEX('%[^0-9]%', @str), 1), '')

       RETURN replace(@str, '-', '')

END

SQL Query to get Number Range in Tabular format

DECLARE @Start INT, @End INT

SELECT  @Start = 0, @End = 100

;WITH Numbers AS (

     SELECT @Start Num

     UNION ALL

     SELECT Num + 1

     FROM Numbers

     WHERE Num < @End

)

SELECT  *

FROM    Numbers

OPTION (MAXRECURSION 0);