SQLCE query alternative for SQL Server SQL

In SQL Server, you can use convenient features such as COUNT(DISTINCT db_field) or YEAR(orderdate). However, SQL functions such as COUNT(DISTINCT ...) and YEAR are not available for SQL Server Compact Edition, therefore, one has to write alternatives to get them to work. below are the example.

SQLCE Alternative to COUNT(DISTINCT ..)

//In SQL Server, will not work in SQLCE 
string query_SQLServer="SELECT COUNT(DISTINCT householdid) FROM customer c JOIN order o ON c.customerid=o.customerid";

//In SQLCE, will work for both SQL Sever and SQLCE
string query_SQLCE="SELECT COUNT(*) FROM (SELECT DISTINCT householdid FROM customer c JOIN order o ON c.customerid=o.customerid) a";

SQLCE Alternative to YEAR()

//In SQL Server, will not work in SQLCE 
string query2_SQLServer="SELECT YEAR(orderdate) FROM customer c JOIN order o ON c.customerid=o.customerid";

//In SQLCE, will work for both SQL Sever and SQLCE
string query2_SQLCE="SELECT DATEPART(year, orderdate) FROM customer c JOIN order o ON c.customerid=o.customerid";

SQLCE Alternative to SQUARE()

//In SQL Server, will not work in SQLCE 
string query_SQLCE="SELECT SQUARE(amount) as amountsqr FROM orders";

//In SQLCE, will work for both SQL Sever and SQLCE
string query_SQLCE="SELECT POWER(amount, 2) as amountsqr FROM orders";