Inserting a Datetime value into SQL Server CE in Webmatrix 3

Question

I want to insert a datetime value into a database using SQL Server Compact Edition in Microsoft Webmatrix 3.

I tried the following query:

INSERT INTO Tutorials ([Tutorial], [StartDate]) 
VALUES ('3d', CONVERT(DATETIME, '07-23-08', 110));

And I got the following error message:

The conversion is not supported. [ Type to convert from (if known) = datetime, Type to convert to (if known) = float ]

Answer 1

Try with

INSERT INTO Tutorials ([Tutorial], [StartDate]) 
VALUES ('3d', CONVERT(DATETIME, '07-23-08', 10));

If you set the style value to 10 the input format must be mm-dd-yy, if you add 100 to the style value the expected format has a four digit year (110 --> mm-dd-yyyy).

For an exhaustive table of the style values look at CAST and CONVERT (SQL Server Compact).

By the way, you could take advantage of an implicit conversion using a date format like yyyymmdd or yyyy-mm-dd:

INSERT INTO Tutorials ([Tutorial], [StartDate]) 
VALUES ('3d', '20080723');