CHECK Constraint for date format required

Question

CHECK Constraint for date format required

Digs

SSCertifiable

Points: 7333
More actions
May 12, 2005 at 11:39 pm

#164389

I have a DATETIME column that I wish to have dates INSTERTED in like
MM/DD/YYYY
I know I can use
SET DATEFORMAT MDY before String dates are inserted.
I know I get SET LANGAUGE TO ENGLISH as default is MM/DD/YYYY
But what would be a good CHECK Constraint to make sure that the date inserted was MM/DD/YYYY from client ???

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

David.Poole SSC Guru Points: 76258 More actions · Answer 1

Well, the field itself will simply reject any date that isn't in the correct format but if you had a stored procedure to do some bespoke error handling you could try

CREATE PROC dbo.usp_InsertDate @sDateParam VARCHAR(10) AS

SET NOCOUNT ON

IF ISDATE(@sDateParam)=0

BEGIN

RAISERROR ('You must enter the date in the correct format',10,1)

RETURN 0

END

INSERT dbo.YourTable etc

LinkedIn Profile

bersileus sacamay-126226 SSChasing Mays Points: 642 More actions · Answer 2

have you tried the following.

1. create a Rule

2. then create a user defined data type(UDT) then associate the rule previously created with the UDT

3. create the table instead of using datetime use, your UDT instead.

hope this helps

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

If it truly is a "DateTime" column, it just doesn't matter what format you store it in so long as it is one recognized by SQL. It will have the date and, in this case, a time of 00:00:00.000. Dates and times are NOT stored in any particular format. Only when they are retrieved can you format them. And, it would be a huge mistake in judgement to store dates in a character based field.

If you have a GUI that's has a field for entering these dates, the format should be checked there and not in SQL because SQL really doesn't care so long as it's a date that can be recognised. Again, if it's a datetime field, you will have to reformat any time you return values from the field.

--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)

Digs SSCertifiable Points: 7333 More actions · Answer 4

Thanks, excellent posts. I have a clear understanding now !

Lowell SSC Guru Points: 323580 More actions · Answer 5

for reference, I like to put a check constraint for a datetime which sets a minimum value...the reason is a date of zero is a valid date (01/01/1900 00:00:00)

for example

ALTER TABLE WHATEVER ADD [ACTIONSENTDT] DATETIME NULL CHECK [ACTIONSENTDT] >'01/01/1995'

...that way an error is raised if a developer sticks a zero in the column, which is assumed to be 01/01/1900 ; this has saved me a lot of times with invalid dates that would have slipped through..

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!