score:7
Tired of twisting myself into knots with date calculations, I created a Table-Valued-Function to calculate elapsed time in Years, Months, Days, Hours, Minutes, and Seconds.
Example
Declare @YourTable table (Name varchar(50),BirthDayNum int, BirthMonthNum int, BirthYearNum int)
Insert Into @YourTable values
('John', 23, 12, 1965),
('Jane', 4, 9, 1975),
('Joe', 6, 3, 1953)
Select A.Name
,B.*
,Age = concat(C.Years,'y ',C.Months,'m')
From @YourTable A
Cross Apply (Select DOB = DateFromParts(A.BirthYearNum,A.BirthMonthNum,A.BirthDayNum)) B
Cross Apply [dbo].[udf-Date-Elapsed](B.DOB,GetDate()) C
Returns
Name DOB Age
John 1965-12-23 51y 3m
Jane 1975-09-04 41y 6m
Joe 1953-03-06 64y 0m
The UDF - May look like overkill, but it is very performant
CREATE FUNCTION [dbo].[udf-Date-Elapsed] (@D1 DateTime,@D2 DateTime)
Returns Table
Return (
with cteBN(N) as (Select 1 From (Values(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) N(N)),
cteRN(R) as (Select Row_Number() Over (Order By (Select NULL))-1 From cteBN a,cteBN b,cteBN c),
cteYY(N,D) as (Select Max(R),Max(DateAdd(YY,R,@D1))From cteRN R Where DateAdd(YY,R,@D1)<=@D2),
cteMM(N,D) as (Select Max(R),Max(DateAdd(MM,R,D)) From (Select Top 12 R From cteRN Order By 1) R, cteYY P Where DateAdd(MM,R,D)<=@D2),
cteDD(N,D) as (Select Max(R),Max(DateAdd(DD,R,D)) From (Select Top 31 R From cteRN Order By 1) R, cteMM P Where DateAdd(DD,R,D)<=@D2),
cteHH(N,D) as (Select Max(R),Max(DateAdd(HH,R,D)) From (Select Top 24 R From cteRN Order By 1) R, cteDD P Where DateAdd(HH,R,D)<=@D2),
cteMI(N,D) as (Select Max(R),Max(DateAdd(MI,R,D)) From (Select Top 60 R From cteRN Order By 1) R, cteHH P Where DateAdd(MI,R,D)<=@D2),
cteSS(N,D) as (Select Max(R),Max(DateAdd(SS,R,D)) From (Select Top 60 R From cteRN Order By 1) R, cteMI P Where DateAdd(SS,R,D)<=@D2)
Select [Years] = cteYY.N
,[Months] = cteMM.N
,[Days] = cteDD.N
,[Hours] = cteHH.N
,[Minutes] = cteMI.N
,[Seconds] = cteSS.N
From cteYY,cteMM,cteDD,cteHH,cteMI,cteSS
)
--Max 1000 years
--Select * from [dbo].[udf-Date-Elapsed] ('1991-09-12 21:00:00.000',GetDate())
Just to Illustrate
The TVF without any secondary string manipulation would return
Select A.Name
,B.*
From @YourTable A
Cross Apply [dbo].[udf-Date-Elapsed](DateFromParts(A.BirthYearNum,A.BirthMonthNum,A.BirthDayNum),GetDate()) B
EDIT - READ ONLY VERSION
Select A.Name
,B.*
,Age = concat(DateDiff(MONTH,B.DOB,GetDate())/12,'y ',DateDiff(MONTH,B.DOB,GetDate()) % 12,'m')
From @YourTable A
Cross Apply (Select DOB = DateFromParts(A.BirthYearNum,A.BirthMonthNum,A.BirthDayNum)) B
Read More
- T-SQL: calculate age then add character to result
- SQL Calculate records of a group then append the result in the table
- Add a row number to result set of a SQL query
- How can I add a character into a specified position into string in SQL SERVER?
- Add default value to an existing column based on If Then Else sql server 2008
- How to calculate age from date of birth and group each member into age range in sql
score:0
Yes it is easier to save as DOB.. But one simple method
select concat( floor(datediff(year, datefromparts(birthyearnum,birthmonthnum,birthdaynum), getdate()))-1, 'y ', datediff(month, datefromparts(birthyearnum,birthmonthnum,birthdaynum), getdate())%12, 'm')
from #yourDates
How age for 1965 is 41y?
Input table:
create table #yourdates(Name varchar(10), BirthdayNum int, BirthMonthNum int, BirthYearNum int)
insert into #yourdates
(Name, BirthdayNum, BirthMonthNum, BirthYearNum) values
('John', 23 , 12 , 1965 )
,('Jane', 4 , 9 , 1975 )
,('Joe ', 6 , 3 , 1953 )
score:0
If you are on 2008 or lesser and can't use datefromparts...
declare @table table ([Name] varchar(4), BirthDayNum int, BirthMonthNum int, BirthYearNum int)
insert into @table
values
('John',23,12,1965),
('Jane',4,9,1975),
('Day',30,3,1990)
;with cte as(
select
[Name],
cast(BirthYearNum as varchar(4)) + '/' + cast(BirthMonthNum as varchar(2)) + '/' + cast(BirthDayNum as varchar(2)) as DOB
from
@table)
select
[Name]
,DOB
,datediff(year,DOB,GETDATE()) as Years
,datediff(month,DOB,GETDATE()) %12 as Months
,rtrim(cast(datediff(year,DOB,GETDATE()) as char(2))) + 'y ' + rtrim(cast(datediff(month,DOB,GETDATE()) %12 as char(2))) + 'm' as Age
from cte
More questions
- Add header title above the query result in SQL Server
- How to dynamically add row(s) in SQL query result
- Add extra rows in result set of query with dummy data in sql server
- Add a specific counter column to a SQL Server result
- SQL Server user defined function to calculate age bracket
- Is it possible to add id column just result of sql query
- how to add commas and single quotes for each character in a string in SQL Server?
- Calculate the age and Insert it to an already column via SQL Server
- SQL Server add 1 to counter based on result of case when
- SQL Server - add a column to the result of a stored procedure
- Add Excel Expressions to a SQL Query Result
- How to add rows with null data dynamically in result table in SQL Server 2012?
- Add character to selected duplicate value in SQL
- How to add a space in every 2 char then 4 char inside the string in sql
- How to add 1 day to current date and have result in format yyyymmdd in SQL Server?
- SQL - Find Duplicates, then add to another table with count
- Calculate SQL Age for Latest item with conditions
- SQL Server: convert to today then add 8 hours: part II
- SQL Server - Hasbytes in function gives different result then in select
- add currency symbol to result of SQL query
- SQL to calculate age based on year and month
- SQL Server 2008: Can you have a query where you do a diff on a count from two tables and then output result
- sql calculate column value then order by it
- List of comma separated IDs in SQL to add or update based on result
More questions with similar tag
- Linked Informix table in MS SQL Server ignoring criteria?
- How constraints/indexes slow down inserts
- SQL Server Database Stuck Single User
- How to speed up SQL script which creates a large table of AlphaNumeric codes
- Ensuring that two column values are related in SQL Server
- What are best practices for database setup from a winform client
- SQL Update Multiple Rows value based on same row
- Case Statement With Multiple Joins
- Query that converts a start time and an end time into hourly records
- Query that merges latest reference in multiple tables into shared rows
- How to assign an XML Schema doc to a SQL Server's table's column with an XML data type
- SQL code to extract values separated by commas in a multivalued field
- Unable to resolve: 'Must declare the scalar variable "@Parameter"`'
- MSSQL connection from PHP on FreeBSD
- TSQL : conditional query
- DECODE( ) function in SQL Server
- Sql Server Operand type clash: date is incompatible with int
- Schedule a stored procedure to run periodically?
- SQL Server query runs slower from ADO.NET than in SSMS
- SQL Server OLEDB error with no details
- SQL Server Split And Table Insert
- How to reset `increment` value back to 1 when data changes?
- Displaying decimal places
- Entity Framework Not Generating Classes for Tables or Procedures
- SqlException (0x80131904) connecting to mdf database
- SQL Insert into select with multiple select queries
- Changing multiple values in an XML column in SQL Server 2008
- Database file-level encryption
- Unable to load php_sqlsrv_55_ts.dll
- SQL Query Syntax Error with percent symbols