In Microsoft SQL Server 2005 and .NET 2.0, I want to convert the current date to a string of this format: "YYYY-MM-DD". For example, December 12th 2009 would become "2009-12-20". How do I do this in SQL.

The context of this SQL statement in the table definiton. In other words, this is the default value. So when a new record is created the default value of the current date is stored as a string in the above format.

I'm trying: SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD]

But SQL server keeps converting that to: ('SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD]')

so the result is just:

'SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD]'

Here's a screen shot of what the Visual Studio server explorer, table, table definition, properties shows:

These wrapper bits are being adding automatically and converting it all to literal string: (N' ')

Here's the reason I'm trying to use something other than the basic DATETIME I was using previously:

This is the error I get when hooking everything to an ASP.NET GridView and try to do an update via the grid view:

Server Error in '/' Application.

The version of SQL Server in use does not support datatype 'date'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.ArgumentException: The version of SQL Server in use does not support datatype 'date'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[ArgumentException: The version of SQL Server in use does not support datatype 'date'.]

Note: I've added a related question to try to get around the SQL Server in use does not support datatype 'date' error so that I can use a DATETIME as recommended.