SQL: Get average time between 2 dates

I have a SQL Statement that lists CrateDate and PaymentDate for all users:  

SELECT     CONVERT(Datetime, tblUserProfileData.PaymentDate, 103) AS Datetime, aspnet_Membership.CreateDate
FROM         tblUserProfileData INNER JOIN
                      aspnet_Membership ON tblUserProfileData.UserId = aspnet_Membership.UserId
WHERE     (tblUserProfileData.PaymentDate <> '') AND (aspnet_Membership.CreateDate > CONVERT(Datetime, '31.01.2009', 103)) AND 
                      (aspnet_Membership.ApplicationId = '189bdd21-fc7a-123d-8a12-1706b1d6a329')

Open in new window


Now I need to find out how many days there is between CreateDate and PaymentDate for an average user. Example: The SQL returns "10" if the average between CreateDate and PaymentDate is 10 days.

Hope someone please can help me solving this :)
LVL 1
webressursAsked:
Who is Participating?
 
John_VidmarConnect With a Mentor Commented:
SELECT  daysAverage = AVG(DATEDIFF(dd,b.CreateDate,a.PaymentDate))
FROM    tblUserProfileData  a
JOIN    aspnet_Membership   b  ON a.UserId = b.UserId
WHERE   a.PaymentDate <> ''
AND     b.CreateDate > CONVERT(Datetime, '31.01.2009', 103)

Open in new window

0
 
SharathData EngineerCommented:
Can you post some sample result of your query and the expected result?
Are you looking for datediff between CreateDate and PaymentDate?
You have used Datetime as column alias. One suggestion is not to use keywords as aliases. If you really want, you need to use it as [Datetime].
0
 
webressursAuthor Commented:
Worked perfect, thank you :)
0
All Courses

From novice to tech pro — start learning today.