Extract date from datetime column - SQL Server Compact

Question

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.

My column signup_date is a DateTime with a value 04-09-2016 09:05:00.

What I've tried so far without success:

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

I found that I could use DATEPART function, but that would force me to concat the values, is this the right path to follow? If so, how do I concat as Y-m-d?

SELECT DATEPART(month, signup_date)  

Answer 1

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)

Answer 2

Most of the answers seek to achieve same thing but the explanation to the codes is not enough

CONVERT(date, Date_Updated, 120)

this code does the conversion with mssql. The first item 'date' is the datatype to return. it could be 'datetime', 'varchar', etc. The second item 'Date_Updated' is the name of the column to be converted. the last item '120' is the date style to be returned. There are various styles and the code entered will determine the output. '120' represent YYYY-MM-DD. Hope this helps