You're updating your existing column with the values which is still a date column. If you pass it a varchar, it will be converted to a datetime and be stored...

Using the newer views, that John suggested:
USE master;
GO

SELECT GETDATE() AS date_time,
       d.[name] AS DBName,
       COUNT(c.connection_id) AS Connections,
 ...

What you're trying to do there is use a variable to declare a schema and table name. The SQL you're attempting to run would basically translate into something like:
DECLARE...

Should...