SQL Server getdate() to a string like "2009-12-20"

Question

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.

Answer 1

CONVERT(CHAR(10), MyDateValue, 120)

However, why not store date as, er, "date" or "smalldatetime"? And use GETDATE() as default

Reasons to do it properly

• implicit conversions because of datatype precedence
• language dependent (don't mention ISO: SQL Server does not play well with date only ISO pre 2008)
• 10 bytes to store, or 12 as varchar. Worse if time is needed (smalldatetime is 4)

Edit:

You don't use a SELECT for a default. You use a function or a constant.

From CREATE TABLE: ...DEFAULT constant_expression...

So wrap the CONVERT in a UDF. Or use GETDATE().

And why are you using style 102?

Answer 2

http://www.sql-server-helper.com/tips/date-formats.aspx

For a list of all the date time formats.

Answer 3

If you REALLY must store your date as VARCHAR (there's really no good reasons for that....), then you'd have to define this in your table definition:

CREATE TABLE yourTable
(........,
 TimestampString NVARCHAR(50) DEFAULT CONVERT(VARCHAR(50), GETDATE(), 102),
 ..........)

Drop the AS [YYYY.MM.DD] part in your statement and you should be fine.

And please: a date string will never need 1024 characters! Make the NVARCHAR field only as long as they really need to be....

But seriously: I would ALWAYS store dates as DATETIME fields - if you absolutely need to have a string representation, why not just add a computed field for that:

ALTER TABLE YourTable
  ADD TimestampString AS CONVERT(NVARCHAR(50), YourDateField, 102) PERSISTED

This way, you get a new computed field "TimestampString", which always represents the 102-style VARCHAR representation of your DATETIME field, and then you can do all date computations on a DATETIME field as it should be.