Transform varchar to date data type then calculate average

1

Hi all,
I am trying to take a date field which is of varchar data type transform it into date data type and then use it to calculate the average of that date.
I have tried a few different ways but the syntax must be incorrect.
Any suggestions welcome please.

Thank you

2

Please provide a sampling of data as follows

Create table #buju(banton varchar(50), miles int)

Insert into #buju
Select '2019-01-01', 3

3 
SELECT
    DATEADD(DAY, 0, AVG(DATEDIFF(DAY, 0, CAST(date_varchar AS date)))) AS avg_date
FROM ( VALUES('20190101'),('20190201') ) AS dates(date_varchar)
--FROM ( VALUES('20190101'),('20190201'),('20190301') ) AS dates(date_varchar)
4

2012-08-01 20120801.00000
1970-01-01 19700101.00000
1988-01-01 19880101.00000
2012-08-01 20120801.00000
1967-01-01 19670101.00000
1982-01-01 19820101.00000
2001-01-01 20010101.00000
2012-01-01 20120101.00000
1999-01-01 19990101.00000
1997-01-01 19970101.00000
1995-01-01 19950101.00000
2010-01-01 20100101.00000
1996-01-01 19960101.00000

the date on the left is formatted using this line CONVERT(date,LEFT(dom.Value,8),112) and the one on the right is the value stored. how can i use the value on the left to calculate the average age?
or do you have other suggestions?
thanks in advance.

5

2012-08-01 20120801.00000
1970-01-01 19700101.00000
1988-01-01 19880101.00000
2012-08-01 20120801.00000
1967-01-01 19670101.00000
1982-01-01 19820101.00000
2001-01-01 20010101.00000
2012-01-01 20120101.00000
1999-01-01 19990101.00000
1997-01-01 19970101.00000
1995-01-01 19950101.00000
2010-01-01 20100101.00000
1996-01-01 19960101.00000

the date on the left is formatted using this line CONVERT(date,LEFT(dom.Value,8),112) and the one on the right is the value stored. how can i use the value on the left to calculate the average age?
or do you have other suggestions?
thanks in advance.

1 Like
6

Average age is a completely different q from "average of that date".

7

So how do I do it?

What are your suggestions?

Thanks

8

The goal is not clear. An age cannot be calculated from a single date.

9 
create table #confiz(dob date);
insert into #confiz
select '2012-08-01' union 
select '1970-01-01' union 
select '1988-01-01' union 
select '2012-08-01' union 
select '1967-01-01' union 
select '1982-01-01' union 
select '2001-01-01' union 
select '2012-01-01' union 
select '1999-01-01' union 
select '1997-01-01' union 
select '1995-01-01' union 
select '2010-01-01' union 
select '1996-01-01' 

;with ages
as
(
select datediff(year, dob, getdate()) _ages,  * 
  From #confiz
)
select AVG(_ages) from ages
--24 is the average age
drop table #confiz
1 Like
10 
SELECT CAST(AVG(age) AS decimal(5, 2)) AS avg_age
FROM #confiz
CROSS APPLY (
    SELECT CAST(DATEDIFF(YEAR, dob, GETDATE()) -
        CASE WHEN CONVERT(varchar(5), dob, 101) > 
                  CONVERT(varchar(5), GETDATE(), 101) 
             THEN 1 ELSE 0 END AS decimal(5, 2)) AS age
) AS age_calc
2 Likes
11

thank you.
i have applied your logic to the data here and it has worked like a dream.
cheers.