Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How do you add a constraint for a VARCHAR(10) column to checks the Date format as yyyy/mm/dd Expand / Collapse
Author
Message
Posted Wednesday, April 2, 2014 2:51 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 6:30 PM
Points: 9, Visits: 147
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 )
Post #1557703
Posted Wednesday, April 2, 2014 2:59 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:55 AM
Points: 13,447, Visits: 12,309
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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1557707
Posted Wednesday, April 2, 2014 3:15 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 9:15 PM
Points: 3,136, Visits: 11,492
if object_id('dbo.tablename','U') is not null begin  drop table dbo.tablename end
go
create table dbo.tablename(
ID int not null identity(1,1) primary key clustered,
SDate varchar(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)


Post #1557712
Posted Wednesday, April 2, 2014 3:56 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:27 AM
Points: 37,076, Visits: 31,637
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.


+1000! 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1557720
Posted Wednesday, April 2, 2014 4:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 6:30 PM
Points: 9, Visits: 147
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.
Post #1557731
Posted Wednesday, April 2, 2014 4:42 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: 2 days ago @ 6:30 PM
Points: 9, Visits: 147
I even recommend that the developers modify the code in the programs to check for invalid date format with no prevail.
Post #1557732
Posted Wednesday, April 2, 2014 5:00 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:27 AM
Points: 37,076, Visits: 31,637
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1557736
Posted Wednesday, April 2, 2014 5:17 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 5:49 PM
Points: 1,787, Visits: 5,722
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


  • MMGrid Addin
  • MMNose Addin


  • Forum Etiquette: How to post Reporting Services problems
  • Forum Etiquette: How to post data/code on a forum to get the best help - by Jeff Moden
  • How to Post Performance Problems - by Gail Shaw

  • Post #1557740
    « Prev Topic | Next Topic »

    Add to briefcase

    Permissions Expand / Collapse