Check for Date

I want to enforce users to enter a date or leave the field null into a
varchar field.
We do not own the code to our database so I can not change the field to
a date/Time (It would violate our contract). I was thinking we could
enter a check constraint (This is OK per our contact) into the database
but I am not sure how.
THE TABLE IS CUST_ORDER_LINE AND THE FIELD IS USER_7.
Thanks for any help.Hi, Watson
You can use:
a) the ISDATE() function (which accepts any date format),
b) a LIKE expression (which accepts a given pattern, but cannot easily
check if the date is valid), or
c) a combination of the above
For example:
ALTER TABLE CUST_ORDER_LINE
ADD CONSTRAINT CK_CUST_ORDER_LINE_USER_7
CHECK (ISDATE(USER_7)<>0)
or:
ALTER TABLE CUST_ORDER_LINE
ADD CONSTRAINT CK_CUST_ORDER_LINE_USER_7
CHECK (USER_7 LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]')
or:
ALTER TABLE CUST_ORDER_LINE
ADD CONSTRAINT CK_CUST_ORDER_LINE_USER_7
CHECK (ISDATE(USER_7)<>0
AND USER_7 LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]')
Razvan
Watson SQL wrote:
> I want to enforce users to enter a date or leave the field null into a
> varchar field.
> We do not own the code to our database so I can not change the field to
> a date/Time (It would violate our contract). I was thinking we could
> enter a check constraint (This is OK per our contact) into the database
> but I am not sure how.
> THE TABLE IS CUST_ORDER_LINE AND THE FIELD IS USER_7.
> Thanks for any help.|||You could have a CHECK constraint as shown below:
ALTER TABLE YourTableName ADD CONSTRAINT CheckDate CHECK (ISDATE(ColumNName)
= 1 or ColumnName IS NULL)
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Watson SQL" <APHILLEY@.WATSONFURNITURE.COM> wrote in message
news:1152544408.938003.176800@.h48g2000cwc.googlegroups.com...
I want to enforce users to enter a date or leave the field null into a
varchar field.
We do not own the code to our database so I can not change the field to
a date/Time (It would violate our contract). I was thinking we could
enter a check constraint (This is OK per our contact) into the database
but I am not sure how.
THE TABLE IS CUST_ORDER_LINE AND THE FIELD IS USER_7.
Thanks for any help.|||>I want to enforce users to enter a date or leave the field null into a
> varchar field.
> THE TABLE IS CUST_ORDER_LINE AND THE FIELD IS USER_7.
Try:
ALTER TABLE dbo.CUST_ORDER_LINE
ADD CONSTRAINT CK_CUST_ORDER_LINE_USER_7
CHECK (USER_7 IS NULL OR ISDATE(USER_7) = 1)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Watson SQL" <APHILLEY@.WATSONFURNITURE.COM> wrote in message
news:1152544408.938003.176800@.h48g2000cwc.googlegroups.com...
>I want to enforce users to enter a date or leave the field null into a
> varchar field.
> We do not own the code to our database so I can not change the field to
> a date/Time (It would violate our contract). I was thinking we could
> enter a check constraint (This is OK per our contact) into the database
> but I am not sure how.
> THE TABLE IS CUST_ORDER_LINE AND THE FIELD IS USER_7.
> Thanks for any help.
>|||ALTER TABLE CUST_ORDER_LINE ADD CONSTRAINT constraintname CHECK(ISDATE(USER_7))
No need to explicitly allow NULL. If a CHECK constraint evaluate to TRUE or UNK, the modification is
allowed.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Watson SQL" <APHILLEY@.WATSONFURNITURE.COM> wrote in message
news:1152544408.938003.176800@.h48g2000cwc.googlegroups.com...
>I want to enforce users to enter a date or leave the field null into a
> varchar field.
> We do not own the code to our database so I can not change the field to
> a date/Time (It would violate our contract). I was thinking we could
> enter a check constraint (This is OK per our contact) into the database
> but I am not sure how.
> THE TABLE IS CUST_ORDER_LINE AND THE FIELD IS USER_7.
> Thanks for any help.
>