Hour and WeekNum functions not recognized in Power Pivot Query

Here is how I did it.  Open a blank EXCEL workbook. Then click Power Pivot - Manage, which open the Power Pivot Window. Select Get External Data - From Other Sources which open the Table Import Wizard. Select Microsoft SQL Server then hit Next. In the new window select the proper server from the dropdown list, enter user name and password and the Database name and hit next.  In the new window select the radio button for Write a query that will specify the data to import and hit Next.  In that space you enter the query, wich was in my original message(except I didn't have Select at the beginning).  As you enter the query you can hit Validate and it tells if the the query is OK.  The SQL I entered validates till I get to Hour=Hour(date/time field).  When I  validate it tells me that Hour is not a recognized function, Same with WeekNum, although theyare listed as functions elsewhere in documentation.  I have tried format with no success, but DatePart works for a couple of the lines as does Day.  I need to have Hour and WeekNum for the level of users that will be using this.  I can get around the problem by having a query that runs in ACCESS and gets all of the data including all of the derived fields, and creates a table that EXCEL can load data from.  But that is not clean and requires 2 applications when I should be able to do it in just EXCEL.  So., the problem is why do Hour and WeekNum not work as functions, and is there a work around where I don't have to use ACCESS.  BTW the date and time field are dateTime data types

Thanks