Calculate Age From Birthdate Column (T-SQL)

You can have a few options to calculate age in years from a date of birth column. When the table gets bigger, the performance can be an issue.
Most important thing you should do is to make sure that you have appropriate indexes in place.
The following scripts have the query to retrieve age from the table and the two indexes I have created on the table.

USE myDB
GO
--CREATE NONCLUSTERED INDEX idx_Birthday
--ON [dbo].[myTable] ([birthdate])
--GO

 
--CREATE NONCLUSTERED INDEX idx_Birthday_myTableid
--ON [dbo].[myTable] ([birthdate])
--INCLUDE ([myTableid])
--GO

 

--Inline 1
SELECT [birthdate]
  , DATEDIFF(yy, [birthdate], current_timestamp) - CASE WHEN
(MONTH([birthdate]) > MONTH(current_timestamp)) OR (MONTH([birthdate]) = MONTH(current_timestamp)
AND DAY([birthdate]) > DAY(current_timestamp)) THEN 1 ELSE 0 END
  FROM  [dbo].[myTable]
  WHERE [birthdate] is not null
--order by myTableid

--Inline 2
  SELECT myTableid, [birthdate]
  , CASE WHEN
 (DATEADD(year,DATEDIFF(year, [birthdate]  ,current_timestamp) , [birthdate]) > current_timestamp)
THEN DATEDIFF(year, [birthdate]  ,current_timestamp) -1
ELSE DATEDIFF(year, [birthdate]  ,current_timestamp) END
  FROM  [dbo].[myTable]
  WHERE [birthdate] is not null 
 --order by myTableid

 --Inline 3
 SELECT  [birthdate],FLOOR((CAST (current_timestamp AS INT) - CAST([birthdate] AS INT)) / 365.25) AS Age
  FROM  [dbo].[myTable]
  WHERE [birthdate] is not null
--order by myTableid


--Scalar UDF
  Select  [birthdate], [dbo].[udfGetAge]([birthdate])    
  FROM  [dbo].[myTable]
  WHERE [birthdate] is not null
  --order by myTableid


--Computed Column
--Cannot set Persisted because current_timestamp is not  deterministic
  Select  [birthdate], Age   
  FROM  [dbo].[myTable]
  WHERE [birthdate] is not null


  

 

Find a Day From a Sequenced Weekday in a Selected Month

DECLARE @tbl TABLE (Yr INT,Mon VARCHAR(50),Dy VARCHAR(50),Dyno INT)

INSERT INTO @tbl(Yr, Mon, Dy, Dyno)
SELECT 2010,'Jan','Sun',2 UNION ALL
SELECT 2005,'Jan','Mon',3 UNION ALL
SELECT 1995,'Feb','Sun',1 UNION ALL
SELECT 2000,'Feb','Wed',4 UNION ALL
SELECT 1982,'Mar','Tue',2 

;with mycte as
(
select Yr, Mon, Dy, Dyno, 
CAST(Mon +'1 '+ cast(Yr as char(4)) as datetime) DayOne, 
datepart(weekday,CAST(Mon +'1 '+ cast(Yr as char(4)) as datetime)) as WeekNumDayOne, 
CASE Dy
WHEN  'Sun' THEN 1
WHEN  'Mon' THEN 2  
WHEN  'Tue' THEN 3
WHEN  'Wed' THEN 4
WHEN  'Thu' THEN 5
WHEN  'Fri' THEN 6
WHEN  'Sat' THEN 7
END WeekNum
FROM @tbl)

SELECT Yr, Mon, Dy, Dyno
,dateadd(day, (Dyno-case when WeekNumDayOne>WeekNum THEN 0 Else 1 END)*7-(WeekNumDayOne - WeekNum), DayOne) as dt
FROM mycte
Order by yr
/*

Yr	Mon	Dy	Dyno	dt
1982	Mar	Tue	2	1982-03-09 00:00:00.000
1995	Feb	Sun	1	1995-02-05 00:00:00.000
2000	Feb	Wed	4	2000-02-23 00:00:00.000
2005	Jan	Mon	3	2005-01-17 00:00:00.000
2010	Jan	Sun	2	2010-01-10 00:00:00.000

http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/dbfcdeb0-0715-461a-a7c8-49ecee77518f
*/

Convert Day of Week Name to Day Number

SELECT CASE LEFt(DATENAME(DW,getdate()),3)
WHEN ‘Sun’ THEN 1
WHEN ‘Mon’ THEN 2
WHEN ‘Tue’ THEN 3
WHEN ‘Wed’ THEN 4
WHEN ‘Thu’ THEN 5
WHEN ‘Fri’ THEN 6
WHEN ‘Sat’ THEN 7
END WeekNum

Convert to week name from week number:

https://jingyangli.wordpress.com/2010/06/02/convert-day-of-week-number-to-day-name/