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.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/