Error 4108: Windowed functions can only appear in the SELECT or ORDER BY clauses
One method is with a common table expression like the example below.  Note that the preceding statement must be terminated with a semicolon.

WITH cte AS (
SELECT  a.database_name ,
        a.collection_time ,
        ROW_NUMBER() OVER ( PARTITION BY CONVERT (DATE, SWITCHOFFSET(CAST (a.collection_time AS DATETIMEOFFSET),
                                                              '+00:00')) ORDER BY a.collection_time DESC ) AS row_ID
FROM    MDWDB.snapshots.disk_usage a
WHERE   a.database_name = 'MDWDB'
    )
SELECT database_name
    ,collection_time
    ,row_ID
FROM cte
WHERE Row_ID = 1;