Search code examples
sqlsql-server-ce

Extract date from datetime column - SQL Server Compact

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)
Solution

  • 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)