Wednesday, May 19, 2010

Age calculation with SQL Server

There seem to be many different methods being suggested to calculate an age in SQLServer.  Some are quite complex but most are simply wrong.  This is by far the simplest and accurate method that I know.

Declare @Date1 datetime
Declare @Date2 datetime


Select @Date1 = '15Feb1971'Select @Date2 = '08Dec2009'select CASE
WHEN dateadd(year, datediff (year, @Date1, @Date2), @Date1) > @Date2
THEN datediff (year, @Date1, @Date2) - 1
ELSE datediff (year, @Date1, @Date2)END as Age
 
--
happy coding 

Ideal SQL Query For Handling Error & Transcation in MS SQL

BEGIN TRY BEGIN TRAN --put queries here COMMIT; END TRY BEGIN CATCH IF @@TRANCOUNT>0 BEGIN SELECT @@ERROR,ERRO...