February 11, 2010 at 7:06 am
Table1
ID int
Enter varchar(10)
I want to restrict Enter column to insert only date YYYY-MM-DD
any clue?
February 11, 2010 at 7:45 am
My recommendation, don't use VARCHAR(10) to store dates, use the datetime data type.
February 11, 2010 at 7:49 am
I agree with Lynn,
a Datetime presents a much better/faster/smaller/easier solution, but if you somehow are required(obliged) to use the varchar, then use a contraint with a user defined function to assure the desired format.
February 11, 2010 at 7:50 am
well, you've got a logical error first:
dates should not be varchars...use datetime datatypes.
CREATE TABLE Table1
(ID int,
Enter datetime)
second, if you want a specific format, validating it to be a specific format should be done in in the application/presentation layer.
you can do it on the database with a CHECK constraint, but will your application gracefully handle an error returned from the server?
anyway, here's what you are asking for:
CREATE TABLE Table1
(ID int,
Enter varchar(10) )
ALTER Table Table1 ADD CONSTRAINT CK_Table1_SpecialFormat CHECK (Enter LIKE '%[0-9][0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9]%')
Lowell
February 11, 2010 at 12:57 pm
In addition to the other posts, if you cannot use a datetime data type and absolutely must use a string data type, you should be using a unambiguous date. The format YYYY-MM-DD can also be interpreted as YYYY-DD-MM.
In SQL Server 2005, the format YYYYMMDD is the format you would use. That format will always be interpreted correctly regardless of regional settings.
You should also use a CHAR data type, since you are going to always want 8 characters all the time.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 11, 2010 at 1:58 pm
As other people have suggested, you would be better off using a datetime column.
This will verify the correct format and ensure that it is a valid date.
ALTER TABLE dbo.MyTable
CONSTRAINT CHK_MyTable__MyDateCol
CHECK
(
case
when MyDateCol not like '[1-2][0-9][0-9][0-9]-[0-1][0-9]-[0-3][0-9]' then 0
when isdate(replace(MyDateCol,'-','')) <> 1 then 0
else 1
end = 1
)
Edit: revised check on first charter of month to be [0-1],insted of [1-2]
February 11, 2010 at 3:15 pm
Michael Valentine Jones (2/11/2010)
As other people have suggested, you would be better off using a datetime column.This will verify the correct format and ensure that it is a valid date.
ALTER TABLE dbo.MyTable
CONSTRAINT CHK_MyTable__MyDateCol
CHECK
(
case
when MyDateCol not like '[1-2][0-9][0-9][0-9]-[1-2][0-9]-[0-3][0-9]' then 0
when isdate(replace(MyDateCol,'-','')) <> 1 then 0
else 1
end = 1
)
Only one problem with the above - and that is what date this actually represents: 2010-12-01
Is that January 12, 2010 or December 1, 2010? Which one is correct will depend upon the regional settings of the client entering the data, and could be wrong if the client reading the data has different settings.
In other words, the above can be interpreted by SQL Server as YYYY-DD-MM or YYYY-MM-DD.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 11, 2010 at 3:41 pm
Jeffrey Williams-493691 (2/11/2010)
Michael Valentine Jones (2/11/2010)
As other people have suggested, you would be better off using a datetime column.This will verify the correct format and ensure that it is a valid date.
ALTER TABLE dbo.MyTable
CONSTRAINT CHK_MyTable__MyDateCol
CHECK
(
case
when MyDateCol not like '[1-2][0-9][0-9][0-9]-[1-2][0-9]-[0-3][0-9]' then 0
when isdate(replace(MyDateCol,'-','')) <> 1 then 0
else 1
end = 1
)
Only one problem with the above - and that is what date this actually represents: 2010-12-01
Is that January 12, 2010 or December 1, 2010? Which one is correct will depend upon the regional settings of the client entering the data, and could be wrong if the client reading the data has different settings.
In other words, the above can be interpreted by SQL Server as YYYY-DD-MM or YYYY-MM-DD.
I was aware of that potential problem, and that is why I used the REPLACE to remove the - characters from the date to change it to an unambiguous format of YYYYMMDD before using the ISDATE function to verify it is a valid date.
The OP said the format is YYYY-MM-DD, so this check should do the job.
February 11, 2010 at 4:02 pm
Michael Valentine Jones (2/11/2010)[h]I was aware of that potential problem, and that is why I used the REPLACE to remove the - characters from the date to change it to an unambiguous format of YYYYMMDD before using the ISDATE function to verify it is a valid date.
The OP said the format is YYYY-MM-DD, so this check should do the job.
I saw that - but just removing the dashes won't fix the problem if the date being passed in is from a system where the regional settings define the format as YYYY-DD-MM. For example, is this date: 2010-03-05, March 5th or May 3rd? When you replace the dashes (20100305) it will be a valid date and will be interpreted as March 5th - which may not be the right date. Also, if the date passed in is 2010-20-01 for January 20th - your validation will fail even when it is a valid date.
I'm just trying to point out why that format is going to cause problems for the OP, and hopefully convince the OP to use the proper data type of datetime. We should also point out to the OP that the only way to insure that you have a valid datetime data type is to not pass it in as a character string in that format.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
February 11, 2010 at 4:13 pm
Jeffrey Williams-493691 (2/11/2010)
Michael Valentine Jones (2/11/2010)[h]I was aware of that potential problem, and that is why I used the REPLACE to remove the - characters from the date to change it to an unambiguous format of YYYYMMDD before using the ISDATE function to verify it is a valid date.
The OP said the format is YYYY-MM-DD, so this check should do the job.
I saw that - but just removing the dashes won't fix the problem if the date being passed in is from a system where the regional settings define the format as YYYY-DD-MM. For example, is this date: 2010-03-05, March 5th or May 3rd? When you replace the dashes (20100305) it will be a valid date and will be interpreted as March 5th - which may not be the right date. Also, if the date passed in is 2010-20-01 for January 20th - your validation will fail even when it is a valid date.
I'm just trying to point out why that format is going to cause problems for the OP, and hopefully convince the OP to use the proper data type of datetime. We should also point out to the OP that the only way to insure that you have a valid datetime data type is to not pass it in as a character string in that format.
The check constraint does exactly what the OP requested, which is to ensure that the date is a valid date in format YYYY-MM-DD.
If the date passed is 2010-20-01, then the check constraint will correctly reject the date. It is not my validation that would have failed; it is the validation of the application that sent a date in the incorrect format that would have failed.
I agree that a date column would be better, but neiter you nor I know the reason behind this question. This might be a column on an existing table where making a change would not really be possible, and they just want to make sure the data is at least a valid date.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply