I'm using SQL Server Compact 4.0 version, and although it might seem a simple thing to find in google, the examples I've tried none of them work.
SELECT FORMAT(signup_date, 'Y-m-d') AS signup_date;
SELECT CONVERT(signup_date, GETDATE()) AS signup_date
SELECT CAST(data_registo, date) AS signup_date
SELECT DATEPART(month, signup_date)
SQL Server Compact has no date type.
If you don't want to see the time, convert the datetime value to a string:
SELECT CONVERT(nvarchar(10), GETDATE(), 120)
(This has been tested and actually works against SQL Server Compact)