July 18, 2017 at 8:52 am
Hello,
I need Year, Months and Days from date of birth column from age table.
Can you please help me to extract the data?
Attached sample file for rerference.
July 18, 2017 at 9:05 am
some discussion here
https://www.sqlservercentral.com/Forums/1867110/Performing-a-Right-and-Comprehensive-Age-Calculation
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
July 18, 2017 at 9:10 am
Note the line in my signature about the best way to get help. Pictures aren't the easiest because we can't copy/paste from an image.
That said, what you are trying to do looks very simple. What have you tried so far? Where are you stuck?
-- Itzik Ben-Gan 2001
July 18, 2017 at 9:35 am
I wonder if something like this would work.
DECLARE @Date datetime = '20150702'
SELECT YEAR( DATEDIFF(DD, @Date, GETDATE())) - 1900,
MONTH( DATEDIFF(DD, @Date, GETDATE())) - 1,
DAY( DATEDIFF(DD, @Date, GETDATE()));
July 18, 2017 at 10:56 am
Luis Cazares - Tuesday, July 18, 2017 9:35 AMI wonder if something like this would work.
DECLARE @Date datetime = '20150702'SELECT YEAR( DATEDIFF(DD, @Date, GETDATE())) - 1900,
MONTH( DATEDIFF(DD, @Date, GETDATE())) - 1,
DAY( DATEDIFF(DD, @Date, GETDATE()));
It seems like this code is not extracting the expected result.
Here is the output for date
Date Result
2015-07-17 00:00:00.000 2 Years 0 Months 3 Days
SELECT CAST(YEAR( DATEDIFF(DD, @Date, GETDATE())) - 1900 AS varchar(4)) + ' Years '+
CAST(MONTH( DATEDIFF(DD, @Date, GETDATE())) - 1 AS VARCHAR(2)) +' Months '+
CAST(DAY( DATEDIFF(DD, @Date, GETDATE())) AS VARCHAR(2))+' Days '
July 18, 2017 at 11:07 am
EasyBoy - Tuesday, July 18, 2017 10:56 AMLuis Cazares - Tuesday, July 18, 2017 9:35 AMI wonder if something like this would work.
DECLARE @Date datetime = '20150702'SELECT YEAR( DATEDIFF(DD, @Date, GETDATE())) - 1900,
MONTH( DATEDIFF(DD, @Date, GETDATE())) - 1,
DAY( DATEDIFF(DD, @Date, GETDATE()));It seems like this code is not extracting the expected result.
Here is the output for dateDate Result
2015-07-17 00:00:00.000 2 Years 0 Months 3 Days
SELECT CAST(YEAR( DATEDIFF(DD, @Date, GETDATE())) - 1900 AS varchar(4)) + ' Years '+CAST(MONTH( DATEDIFF(DD, @Date, GETDATE())) - 1 AS VARCHAR(2)) +' Months '+
CAST(DAY( DATEDIFF(DD, @Date, GETDATE())) AS VARCHAR(2))+' Days '
I never claimed that the code would extract the expected result. I mentioned that something similar could get the result. 😉
If you want a perfect solution, you need to post correctly.
July 19, 2017 at 2:03 pm
Building off of Luis' excellent solution...
This:
-- Sample data, solution
DECLARE @samples TABLE (dob date);
INSERT @samples VALUES
('20161102'),('20150326'),('20150717'),('20140818'),('20120712'),('20121201'),('20120522');
-- Solution
SELECT dob,
Age = YY + CASE WHEN MM < 6 THEN 0 ELSE 1 END,
calculated_age = YY+' Years '+MM+' Months '+DD+' Days'
FROM @samples s
CROSS APPLY
(
SELECT CAST(YEAR(DATEDIFF(DD, s.dob, GETDATE())) - 1900 AS varchar(4)), --Years
CAST(MONTH(DATEDIFF(DD, s.dob, GETDATE())) - 1 AS VARCHAR(2)), --Months
CAST(DAY(DATEDIFF(DD, s.dob, GETDATE())) AS VARCHAR(2)) --Days
) Parts(YY,MM,DD);
Returns:
dob Age calculated_age
2016-11-02 1 0 Years 8 Months 17 Days
2015-03-26 2 2 Years 3 Months 27 Days
2015-07-17 2 2 Years 0 Months 4 Days
2014-08-18 3 2 Years 11 Months 3 Days
2012-07-12 5 5 Years 0 Months 8 Days
2012-12-01 5 4 Years 7 Months 19 Days
2012-05-22 5 5 Years 1 Months 28 Days
-- Itzik Ben-Gan 2001
July 20, 2017 at 1:54 am
Alan.B - Wednesday, July 19, 2017 2:03 PMBuilding off of Luis' excellent solution...
This:
-- Sample data, solution
DECLARE @samples TABLE (dob date);
INSERT @samples VALUES
('20161102'),('20150326'),('20150717'),('20140818'),('20120712'),('20121201'),('20120522');-- Solution
SELECT dob,
Age = YY + CASE WHEN MM < 6 THEN 0 ELSE 1 END,
calculated_age = YY+' Years '+MM+' Months '+DD+' Days'
FROM @samples s
CROSS APPLY
(
SELECT CAST(YEAR(DATEDIFF(DD, s.dob, GETDATE())) - 1900 AS varchar(4)), --Years
CAST(MONTH(DATEDIFF(DD, s.dob, GETDATE())) - 1 AS VARCHAR(2)), --Months
CAST(DAY(DATEDIFF(DD, s.dob, GETDATE())) AS VARCHAR(2)) --Days
) Parts(YY,MM,DD);Returns:
dob Age calculated_age
2016-11-02 1 0 Years 8 Months 17 Days
2015-03-26 2 2 Years 3 Months 27 Days
2015-07-17 2 2 Years 0 Months 4 Days
2014-08-18 3 2 Years 11 Months 3 Days
2012-07-12 5 5 Years 0 Months 8 Days
2012-12-01 5 4 Years 7 Months 19 Days
2012-05-22 5 5 Years 1 Months 28 Days
today is the 20th July ..... DECLARE @samples TABLE (dob date);
INSERT @samples VALUES
('20150720'),('20160720');
-- Solution
SELECT dob,
Age = YY + CASE WHEN MM < 6 THEN 0 ELSE 1 END,
calculated_age = YY+' Years '+MM+' Months '+DD+' Days'
FROM @samples s
CROSS APPLY
(
SELECT CAST(YEAR(DATEDIFF(DD, s.dob, GETDATE())) - 1900 AS varchar(4)), --Years
CAST(MONTH(DATEDIFF(DD, s.dob, GETDATE())) - 1 AS VARCHAR(2)), --Months
CAST(DAY(DATEDIFF(DD, s.dob, GETDATE())) AS VARCHAR(2)) --Days
) Parts(YY,MM,DD);
using the above should deliver 2yr/0mth/0day and 1yr/0mth/0day but the code delivers
dob Age calculated_age
2015-07-20 2 2 Years 0 Months 2 Days
2016-07-20 1 1 Years 0 Months 1 Days
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply