How do you add a constraint for a VARCHAR(10) column to checks the Date format as yyyy/mm/dd

Question

How do you add a constraint for a VARCHAR(10) column to checks the Date format as yyyy/mm/dd

vanessa4biz

SSC Veteran

Points: 297
More actions
April 2, 2014 at 2:51 pm

#396526

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 )
Forum Etiquette: How to post Reporting Services problems

[/url]

Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
[/url]

How to Post Performance Problems - by Gail Shaw
[/url]

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

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/

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 2

if object_id('dbo.tablename','U') is not null begin drop table dbo.tablename end

go

create table dbo.tablename(

IDintnot null identity(1,1) primary key clustered,

SDatevarchar(10) not null

)

go

ALTER TABLE dbo.tablename ADD CONSTRAINT CHK_Date_format check

(case when SDate not like '[1-2][0-9][0-9][0-9]/[0-1][0-9]/[0-3][0-9]' then 0

when isdate(SDate) <> 1 then 0

else 1 end = 1)

go

insert into dbo.tablename select sdate = '2014/01/01'

go

insert into dbo.tablename select sdate = '2014/13/01'

go

insert into dbo.tablename select sdate = '2014/1/01'

go

insert into dbo.tablename select sdate = '2004/02/29'

go

insert into dbo.tablename select sdate = '2005/02/29'

go

select * from dbo.tablename

go

if object_id('dbo.tablename','U') is not null begin drop table dbo.tablename end

Results:

(1 row(s) affected)

Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the CHECK constraint "CHK_Date_format". The conflict occurred in database "tempdb", table "dbo.tablename", column 'SDate'.

The statement has been terminated.

Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the CHECK constraint "CHK_Date_format". The conflict occurred in database "tempdb", table "dbo.tablename", column 'SDate'.

The statement has been terminated.

(1 row(s) affected)

Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the CHECK constraint "CHK_Date_format". The conflict occurred in database "tempdb", table "dbo.tablename", column 'SDate'.

The statement has been terminated.

ID SDate

----------- ----------

1 2014/01/01

4 2004/02/29

(2 row(s) affected)

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 3

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.

[font="Arial Black"]+1000![/font] Storing dates and times in non date/time columns is like being barefoot in soaking wet pajama's while trying to get over a barbed wire fence charged to 50KV in the rain with kite string tied to your ear! 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

vanessa4biz SSC Veteran Points: 297 More actions · Answer 4

I agree with you hundred percent but this is a legacy system and management is afraid to change the data type in fear that it may cause other problems.

vanessa4biz SSC Veteran Points: 297 More actions · Answer 5

I even recommend that the developers modify the code in the programs to check for invalid date format with no prevail.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 6

vanessa4biz (4/2/2014)
I agree with you hundred percent but this is a legacy system and management is afraid to change the data type in fear that it may cause other problems.

Understood. In that case (no pun intended), Michael's solution (above) will do the trick for you. It checks for format and functionality.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

mister.magoo SSC-Forever Points: 47174 More actions · Answer 7

If you have to do it, then I would also recommend Michael's code, but with an additional range check suitable to your needs, just to prevent valid but unreasonable dates.

e.g.

ALTER TABLE dbo.tablename ADD CONSTRAINT CHK_Date_format check

(case when SDate not like '[1-2][0-9][0-9][0-9]/[0-1][0-9]/[0-3][0-9]' then 0

when isdate(SDate) <> 1 then 0

else 1 end = 1 and SDate>='2000/01/01' and SDate<'2100/01/01')

However, while management may be right to have concerns about changing a data type, they should have those same concerns about adding a check constraint, which will throw errors for invalid data - something that presumably does not happen now...

MM

select geometry::STGeomFromWKB(0x0106000000020000000103000000010000000B0000001000000000000840000000000000003DD8CCCCCCCCCC0840000000000000003DD8CCCCCCCCCC08408014AE47E17AFC3F040000000000104000CDCCCCCCCCEC3F9C999999999913408014AE47E17AFC3F9C99999999991340000000000000003D0000000000001440000000000000003D000000000000144000000000000000400400000000001040000000000000F03F100000000000084000000000000000401000000000000840000000000000003D0103000000010000000B000000000000000000143D000000000000003D009E99999999B93F000000000000003D009E99999999B93F8014AE47E17AFC3F400000000000F03F00CDCCCCCCCCEC3FA06666666666FE3F8014AE47E17AFC3FA06666666666FE3F000000000000003D1800000000000040000000000000003D18000000000000400000000000000040400000000000F03F000000000000F03F000000000000143D0000000000000040000000000000143D000000000000003D, 0);