August 4, 2016 at 10:33 am
I have a table with a varchar column that takes a date in the MM/DD/YYYY format.
I need to add a constraint to the table so that the values that it take will have to have the format mm/dd/yyyy
August 4, 2016 at 10:44 am
Why don't you simply store it as a date? It'll be more effective than a constraint.
If you seriously need a constraint, check the following article: http://www.sqlservercentral.com/articles/T-SQL/130558/
August 4, 2016 at 10:46 am
August 4, 2016 at 10:54 am
Luis C
I don't have control over the structure of the table the data is landing. They defined it as varchar(25)
I am good at writing functions but I am not sure of the syntax on adding the function as a check constraint
August 4, 2016 at 11:06 am
Here.. Can someone help me with the syntax and I we can add the function as a constraint. I need someone to help me with the ID statement inside.
Now sure how to do the pattern matching
ALTER FUNCTION Claim.FnCheckStringFormat1( @input VARCHAR(100) ) RETURNS INT
as
Begin
Declare @RES int;
Select @RES=0;
IF ISNULL(@input,'') = '' Select @RES=0;
IF ISNULL(@input,'') > ''
Begin
If( @input like '[0-9][0-9]' ) --<<<<<<-- Need some help here
Begin
Select @RES = 1;
End
End
RETURN @RES
End
August 4, 2016 at 11:07 am
Here's an example:
CREATE TABLE CheckTest(
CharDate varchar(25)
)
GO
ALTER TABLE CheckTest ADD CONSTRAINT CK_CharDate CHECK( CharDate = CONVERT( varchar(10), CONVERT( datetime, CharDate, 101), 101));
GO
INSERT INTO CheckTest VALUES( '08/04/2016');
GO
INSERT INTO CheckTest VALUES( '00/04/2016');
GO
INSERT INTO CheckTest VALUES( '18/04/2016');
GO
SELECT * FROM CheckTest
GO
DROP TABLE CheckTest
I also had the check with a string comparison, but might not be needed.
CharDate LIKE '[0-1][0-9]/[0-3][0-9]/[1-9][0-9][0-9][0-9]'
August 4, 2016 at 11:13 am
OK I got it .... The following function works... NOW I need SYNTAX help. How do we add this as a CONSTRAINT to a existing varchar column
ALTER FUNCTION Claim.FnCheckStringFormat1( @input VARCHAR(100) ) RETURNS INT
as
Begin
Declare @RES int;
Select @RES=0;
IF ISNULL(@input,'') = '' Select @RES=0;
IF ISNULL(@input,'') > ''
Begin
If( @input like '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]' AND ISDATE(@input)=1 )
Begin
Select @RES = 1;
End
End
RETURN @RES
End
August 4, 2016 at 11:15 am
Thanks LUIS C
We both posted at the same time.
Thanks for the help
August 4, 2016 at 11:18 am
One more thanks.... LUIS
Your constraint allows me to add NULL values as well. I needed that !
Good job.. I tested the following code and it worked
CREATE TABLE CheckTest(
CharDate varchar(25)
)
GO
ALTER TABLE CheckTest ADD CONSTRAINT CK_CharDate CHECK( CharDate = CONVERT( varchar(10), CONVERT( datetime, CharDate, 101), 101));
GO
INSERT INTO CheckTest VALUES( '08/04/2016');
GO
--INSERT INTO CheckTest VALUES( '00/04/2016');
GO
--INSERT INTO CheckTest VALUES( '18/04/2016');
GO
INSERT INTO CheckTest VALUES( NULL);
GO
SELECT * FROM CheckTest
GO
DROP TABLE CheckTest
August 4, 2016 at 11:22 am
One last thing. Try to stay away from scalar functions, they'll only slow you down. I'm not talking about coding time, but real processing time once the system is live.
August 4, 2016 at 11:27 am
I know what it's like to inherit a database originally developed by an intern. Refactoring the datatypes is the best option, but not if a dozen applications and reporting systems have been using it for years. So, here is what I've done in the past to enforce date validation constraints on a VarChar column. Rather than a check constraint that attempts to validate the format of the string value, you can instead simply have the check constraint attempt to convert the value to a date. It also converts the date to a string in intended format and then compares that back the original value. So, for invalidly formatted string values, you'll either get a conversion error for values that can't be cast as date, or you'll get a false comparison for values not in MM/DD/YYYY format. As an added bonus, it will also fail on non-calendar dates (ex: 02/30/2016).
create table foo
(
foo_date varchar(30) not null
constraint ck_foo_date_mm_dd_yyyy
check (foo_date = convert(char(10),cast(foo_date as date),101))
);
insert into foo (foo_date) values ('2016/04/16');
The INSERT statement conflicted with the CHECK constraint "ck_foo_date_mm_dd_yyyy ".
insert into foo (foo_date) values ('20160422');
The INSERT statement conflicted with the CHECK constraint "ck_foo_date_mm_dd_yyyy ".
insert into foo (foo_date) values ('04222016');
Conversion failed when converting date and/or time from character string.
insert into foo (foo_date) values ('04/22/2016');
(1 row(s) affected)
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply