CODEWITHSUNDEEP
sql-server
Cenk Ten
Cenk Ten

Reputation: 283

SQL Server: how to get average date

I'm trying to get average date for my table result (result.regDate as datetime). I found on another topic following code:

SELECT CAST(AVG(CAST(regDate AS INT)) AS DATETIME) FROM result;

But i'm getting following error:

The explicit conversion of the date data type to float is not permitted.

Am i doing something false or is this statement false?

Upvotes: 1

Views: 4928

Answers (3)

MikeTeeVee
MikeTeeVee

Reputation: 19392

If you're using another Date-Time datatype (i.e. Date or DateTime2 or SmallDateTime), then you need to cast it as DateTime BEFORE casting it as a Float - then cast it back to DateTime (after calling AVG) before implicitly using against (or explicitly casting as) another Date-Time datatype.

Here's the "fool-proof" SQL:

CAST( AVG(CAST(CAST(regDate as DateTime) as Float)) as DateTime)

Caveat: If you are using Dates that fall out of the normal range of dates the DateTime datatype supports (i.e. anytime before the year 1753), then you will need to consider a different approach.

Upvotes: 1

Cenk Ten
Cenk Ten

Reputation: 283

The following code returns the average - but only as year:

SELECT AVG(YEAR(regDate)) FROM results

This helped me.

Upvotes: 1

apomene
apomene

Reputation: 14389

you need to cast it to float not int, Try:

SELECT CAST(AVG(CAST(regDate AS FLOAT)) AS DATETIME) FROM result

Upvotes: 0

Related Questions