The problem here is that if any group name doesn't have a colon in it, the SUBSTRING() command is trying to use a negative number when CHARINDEX() comes up with zero. CHARINDEX() will return zero if it doesn't find the character you are looking for.
So, what are the names of your groups? Find this out by performing the query:
SELECT name FROM dwsystem.dbo.dwgroup
I don't have any group names with colons, but all my group names have spaces in them. If I change the colon in your query to a space, the query succeeds.
In the same way you keep "Public" out (ostensibly because it doesn't have a colon), you need to keep out any group name lacking a colon via another condition in the WHERE clause:
AND CHARINDEX(':', name, 1) > 0
In fact, since that will inherently keep out "Public" as well, you can just use that for a final query of:
DEPARTMENT IN (SELECT SUBSTRING(name, 1, CHARINDEX(' ', name, 1) - 1) AS Department FROM dwsystem.dbo.dwgroup
WHERE gid IN (SELECT gid FROM dwsystem.dbo.DWUserToGroup WHERE uid IN (SELECT uid FROM dwsystem.dbo.dwuser WHERE name = CURRENTUSERLONGNAME()))
AND CHARINDEX(':', name, 1) > 0)
As I said, I get no records back (because I have no groups with colons in the name), but at least the query does not error out on SQL Server.