In one of my interview , interviewer asked me how should you calculate age from date of birth.
As i m not too strong in SQL server , i have started from .Net code for calculating Age from DOB.
but not success ed in that. after searching in books & discussing with friends[Vinay Shah]
come to conclusion that we can get Age from Sql Function DATEDIFF.
in SQL Server 2005 u can use Datediff function
Sample Query = select datediff (year, '1988-05-17 00:00:00.000' , getDate())
from above query you will get the Age in year OUTPUT = 24
instead of 1988-05-17 00:00:00.000 you can enter your DOB & get your Age.
in Oracle
select ROUND((SysDate - DOB)/365) as Age from cust_table
here you we will get age of the customers where DOB should have Date DataType
Using Datediff function we can calculate not only age in years but also following
As i m not too strong in SQL server , i have started from .Net code for calculating Age from DOB.
but not success ed in that. after searching in books & discussing with friends[Vinay Shah]
come to conclusion that we can get Age from Sql Function DATEDIFF.
in SQL Server 2005 u can use Datediff function
Sample Query = select datediff (year, '1988-05-17 00:00:00.000' , getDate())
from above query you will get the Age in year OUTPUT = 24
instead of 1988-05-17 00:00:00.000 you can enter your DOB & get your Age.
in Oracle
select ROUND((SysDate - DOB)/365) as Age from cust_table
here you we will get age of the customers where DOB should have Date DataType
Using Datediff function we can calculate not only age in years but also following
| Unit of time | Query | Result |
| NANOSECOND | SELECT DATEDIFF(NANOSECOND,'2011-09-23 17:15:22.5500000','2011-09-23 17:15:22.55432133') | 4321300 |
| MICROSECOND | SELECT DATEDIFF(MICROSECOND,'2011-09-23 17:15:22.5500000','2011-09-23 17:15:22.55432133') | 4321 |
| MILLISECOND | SELECT DATEDIFF(MILLISECOND,'2011-09-23 17:15:22.004','2011-09-23 17:15:22.548') | 544 |
| SECOND | SELECT DATEDIFF(SECOND,'2011-09-23 17:15:30','2011-09-23 17:16:23') | 53 |
| MINUTE | SELECT DATEDIFF(MINUTE,'2011-09-23 18:03:23','2011-09-23 17:15:30') | -48 |
| HOUR | SELECT DATEDIFF(HH,'2011-09-23 18:03:23','2011-09-23 20:15:30') | 2 |
| WEEK | SELECT DATEDIFF(WK,'09/23/2011 15:00:00','12/11/2011 14:00:00') | 12 |
| DAY | SELECT DATEDIFF(DD,'09/23/2011 15:00:00','08/02/2011 14:00:00') | -52 |
| DAYOFYEAR | SELECT DATEDIFF(DY,'01/01/2011 15:00:00','08/02/2011 14:00:00') | 213 |
| MONTH | SELECT DATEDIFF(MM,'11/02/2011 15:00:00','01/01/2011 14:00:00') | -10 |
| QUARTER | SELECT DATEDIFF(QQ,'01/02/2011 15:00:00','08/01/2011 14:00:00') | 2 |
| YEAR | SELECT DATEDIFF(YY,'01/02/2011 15:00:00','01/01/2016 14:00:00') | 5 |
