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);

SQL Query to get Constraint Information

SELECT ISNULL(c.constraint_name, '') ConstraintName
 ,ISNULL(cfk.table_name, '') FK_Table_name
 ,ISNULL(kcu.column_name, '') FK_Column_name
 ,ISNULL(cpk.table_name, '') PK_Table_name
 ,ISNULL(pkt.column_name, '') PK_COlumn_name
FROM information_schema.REFERENTIAL_CONSTRAINTS c
INNER JOIN information_schema.TABLE_CONSTRAINTS cpk ON c.unique_constraint_name = cpk.constraint_name
INNER JOIN information_schema.TABLE_CONSTRAINTS cfk ON c.constraint_name = cfk.constraint_name
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcu ON c.constraint_name = kcu.constraint_name
INNER JOIN (
 SELECT tci.table_name
  ,kcui.column_name
 FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tci
 INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE kcui ON tci.constraint_name = kcui.constraint_name
 WHERE tci.CONSTRAINT_TYPE = 'PRIMARY KEY'
 ) PKT ON PKT.TABLE_NAME = CPK.TABLE_NAME
GROUP BY c.constraint_name
 ,cfk.table_name
 ,kcu.column_name
 ,cpk.table_name
 ,pkt.column_name
HAVING count(pkt.column_name) = 1
--and cfk.table_name = 'pf_child'
ORDER BY 1
 ,2
 ,3
 ,4

SQL Query to get Foreign Key Details

SELECT f.name AS ForeignKey,

SCHEMA_NAME(f.SCHEMA_ID) SchemaName,

OBJECT_NAME(f.parent_object_id) AS TableName,

COL_NAME(fc.parent_object_id,fc.parent_column_id) AS ColumnName,

SCHEMA_NAME(o.SCHEMA_ID) ReferenceSchemaName,

OBJECT_NAME (f.referenced_object_id) AS ReferenceTableName,

COL_NAME(fc.referenced_object_id,fc.referenced_column_id) AS ReferenceColumnName

FROM sys.foreign_keys AS f

INNER JOIN sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id

INNER JOIN sys.objects AS o ON o.OBJECT_ID = fc.referenced_object_id

Search a text from all Stored Procedures

SELECT obj.Name SPName, sc.TEXT SPText

FROM sys.syscomments sc

INNER JOIN sys.objects obj ON sc.Id = obj.OBJECT_ID

WHERE sc.TEXT LIKE '%' + 'YouTextHere' + '%'

AND TYPE = 'P'

SQL UDF to get No. of Days in a Month

CREATE FUNCTION [dbo].[fn_GetNumDaysInMonth] ( @myDateTime DATETIME )

RETURNS INT

AS

BEGIN

DECLARE @rtDate INT

SET @rtDate = CASE WHEN MONTH(@myDateTime)

IN (1, 3, 5, 7, 8, 10, 12) THEN 31

WHEN MONTH(@myDateTime) IN (4, 6, 9, 11) THEN 30

ELSE CASE WHEN (YEAR(@myDateTime) % 4 = 0

AND

YEAR(@myDateTime) % 100 != 0)

OR

(YEAR(@myDateTime) % 400 = 0)

THEN 29

ELSE 28 END

END

RETURN @rtDate

END

SQL Query to display all User Defined Functions

SELECT name AS function_name

,SCHEMA_NAME(schema_id) AS schema_name

,type_desc

FROM sys.objects

WHERE type_desc LIKE '%FUNCTION%'

SQL UDF to get Currency in Comma seperated

CREATE FUNCTION [dbo].[fn_CurrencyInComma]

(

       @Value DECIMAL(18, 2)

)

RETURNS VARCHAR(50)

AS

BEGIN

       DECLARE       @s VARCHAR(50),

              @ptr SMALLINT

       --FOR INDIA SET 2, AND FOR UK SET 3

       DECLARE @IndiaOrUK SMALLINT = 2

       SELECT @s = CAST(@Value AS VARCHAR(50)),

              @ptr = DATALENGTH(@s) - 5

       WHILE @ptr >= 2

              SELECT @s = STUFF(@s, @ptr, 0, ','),

                     @ptr = @ptr - @IndiaOrUK

       RETURN @s

END