Sql-server – DD/mm/yyyy in varchar(max) needs to be converted to yyyymmdd format

You could try concatenating a valid 'day' on to the YYYY-MM values and use a CASE expression and the ISDATE function.

IF EXISTS (
        SELECT *
        FROM dbo.TEST
        )
    DROP TABLE dbo.TEST
go
CREATE TABLE Test (
    ID INTEGER
    ,YYYY_MM_CHAR CHAR(7) NOT NULL
    ,CONSTRAINT chk_YYYY_MM_CHAR CHECK (
        CASE 
            WHEN ISDATE(YYYY_MM_CHAR + '-01') = 1
                THEN 1
            ELSE 0
            END = 1
        )
    );

INSERT INTO TEST (ID,YYYY_MM_CHAR) VALUES(1,'2014-01')
INSERT INTO TEST (ID,YYYY_MM_CHAR) VALUES(1,'2014-13')

(1 row(s) affected)
Msg 547, Level 16, State 0, Line 9
The INSERT statement conflicted with the CHECK constraint "chk_YYYY_MM_CHAR". The conflict occurred in database "master", table "dbo.Test", column 'YYYY_MM_CHAR'.
The statement has been terminated.

Using XEvents Trace, I can see SQL Server Management Studio is getting confused around the [move] column.

The DML query it uses to update the row actually works, however the query it issues to retrieve the updated row looks like:

DECLARE @Param1 int, @Param2 float,@Param3 float;
SET @Param1 = 1;
SET @Param2 = 2;
SET @Param3 = 3;

SELECT TOP (200) identkey, Move, Wind, Traverse 
FROM tbl_Rv8WindProfile 
WHERE ('Move' = @Param1) 
    AND (Wind = @Param2) 
    AND (Traverse = @Param3);

This looks like a bug in SQL Server Management Studio (SSMS). I tested this with SSMS 17.4, SSMS 2016, SSMS 2012; all of them exhibit this behavior. SSMS is wrapping the [move] column with single-quotes instead of square-brackets or double-quotes as would be expected.

Interestingly, SSMS 2008 R2 won't even open the "Edit top 200 rows" window. It raises an error-dialog containing the text "Invalid prefix or suffix characters (MS Visual Database Tools)".

FYI, the INSERT statement used by SSMS to insert new rows is:

DECLARE @Move int, @Wind float,@Traverse float;
SET @Move = 1;
SET @Wind = 2;
SET @Traverse = 3;

INSERT TOP (200) 
INTO tbl_Rv8WindProfile(Move, Wind, Traverse) 
VALUES (@Move, @Wind, @Traverse)

The INSERT and subsequent SELECT statement do not appear to be executed in the same transaction; in my tests, I ended up with multiple rows in the table, one for each error I received. You may want to manually confirm the table contains the correct data.