The CreateDate is stored in aspnet_Membership, so in order to access it you need to first JOIN to the aspnet_Users table using the "UserName" field and then join from there to the aspnet_Membership table in order to retrieve the CreateDate:
SELECT DISTINCT(UR.UserID), U.FirstName, U.LastName, R.RoleName, ExpiryDate, AM.CreateDate
FROM USERROLES UR
INNER JOIN USERS U ON UR.UserID = U.UserID
INNER JOIN ROLES R ON UR.RoleID = R.RoleID
INNER JOIN aspnet_Users AU ON AU.UserName = U.Username
INNER JOIN aspnet_Membership AM ON AM.UserId = AU.UserId
WHERE UR.ExpiryDate IS NOT NULL
ORDER BY ExpiryDate
However this brings up a bug in the way the reports module filters out SQL scripts that could cause damage to the database. Originally, any administrator could create queries, and if they were to use on of the SQL Commands that changes the database (such as INSERT, or DELETE, or CREATE) they could seriously damage the database. To prevent this, the module filters out these SQL commands, and unfortunatly will filter out the 'Create' in 'CreateDate'. Since in the current version, only SuperUsers can create reports, this filter is not necessary anymore and it has been removed for the next release
So, to cut a long story short, what you want to do is not really possible at the moment because of a limitation in the module that has been fixed for the next release. The SQL above should return the data you want, but the Reports module will remove the word 'Create' from 'CreateDate' (confusing it with the SQL Command: CREATE) which will cause an error in the Report.