Sean Lange (4/2/2014)

---

vanessa4biz (4/2/2014)

---

I have tried:

ALTER TABLE dbo.tablename

ADD CONSTRAINT DF_Date_format

check (SDate LIKE '%[1-2][0-9][0-9][0-9]/[0-1][0-9]/[0-3][0-9]%')

******But this does not work for VARCHAR data type ********

and

I also tried creating a function as shown below but I got an error when trying to add a constraint with this function.

(error is:

'The ALTER TABLE statement conflicted with the CHECK constraint "DF_Date_format". The conflict occurred in database " databasename ", table "dbo.tablename", column 'Date'.)

Create FUNCTION [dbo].[CheckDateFormat]

(@Date varchar(10))

Returns BIT

AS

BEGIN

Declare @RETURN BIT

SELECT @Return =

Case when (substring (@Date, 5, 1) + substring (@Date, 8, 1)) = '//'

then 0

else 1

end

Return @Return

END

ALTER TABLE dbo.tablename

ADD CONSTRAINT DF_Date_format

check

( dbo.CheckDateFormat(SDate) = 0 )

Don't do this!!! Change your datatype to datetime. You don't need to jump through hoops for validation and there are at least a dozen other benefits from using the proper datatype and dozens and dozens of reason NOT to use varchar to hold datetime data.