date() function returns a human readable representation of a timestamp.
So comparing its return value to a timestamp (joindate) is erroneous.
The query you are probably looking for:
PHP Code:
$new_users = $DB_site->query("
SELECT username, email, languageid, userid, joindate
FROM " . TABLE_PREFIX . "user
WHERE joindate >= ".strtotime('2004-10-06')."
AND joindate < ".strtotime('2004-10-07')."
AND usergroupid IN ($ids)
");