May 12, 2005 at 11:39 pm
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 ???
May 13, 2005 at 1:46 am
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
May 13, 2005 at 1:53 am
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
May 15, 2005 at 10:16 am
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
Change is inevitable... Change for the better is not.
May 15, 2005 at 1:02 pm
Thanks, excellent posts. I have a clear understanding now !
May 16, 2005 at 2:03 pm
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
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply