Validation of Today''s date

Question

Validation of Today''s date

Ede Sartori

Newbie

Points: 3
More actions
April 23, 2007 at 2:17 pm

#172091

Hi,
I set the field to return the today's date as a default, but I'd like to validate also that the entered date is bigger the the todays date
CONSTRAINT
df_DateEnrolled DEFAULT GETDATE()FOR DateEnrolled
Plese help
Thanks
Ede

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

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

Aaron Ingold SSCrazy Eights Points: 9070 More actions · Answer 1

If I'm reading you correctly, I wouldn't use a default. All a default allows you to do is specify a value to be entered for new records if no value is explicitly provided. What you want to do is do a check constraint:

ALTER TABLE dbo.MyTable

ADD CONSTRAINT CK__MyTable__DateEnrolled CHECK (DateEnrolled >= GETDATE())

Then use the application to provide a default enroll date of today's date.

Ede Sartori Newbie Points: 3 More actions · Answer 2

I think I figured out, the order it was executing was wrong, I had it switched.

Is this is correct?

CONSTRAINT valid_DateEnrolled CHECK(DateEnrolled>GETDATE()),

CONSTRAINT df_DateEnrolled DEFAULT GETDATE()FOR DateEnrolled

Ede Sartori Newbie Points: 3 More actions · Answer 3

Makes sence what you are saying, but it was a requirenment for my assignment for the course I'm taking.

Christopher Klein SSCrazy Points: 2648 More actions · Answer 4

It's great except that it doesn't seem to work.

create

table #test

(

mydate datetime NOT NULL constraint df_value DEFAULT GETDATE(), constraint chk_valid CHECK (mydate>=GETDATE())

)

declare

@var1 datetime

select @var1= ''

insert into #test(mydate)values(@var1)

select * from #test

drop table #test

GETDATE() returns 04/23/2007. If I pass 04/23/2007 it doesn't work, but 04/24/2007 does. >= does not seem to validate properly.

The odd part is if I pass '' for no value so the default will go it also fails. if I reverse the >= to a < I get a date of 01/01/1900 passed in. No idea why that happens...

I would opt for

CHECK (datediff(dd,mydate,getdate()+1)>0)

Since you said you wanted it greater than todays date.

Didn't mean to hijak the thread but the behavior of the default value just seems odd.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 5

The check constraint is a bad idea.

Even if you fix the obvious bug in the check constraint, the contraint would prevent you from updating a row once the current system date is greater than mydate.

If you want to have some kind validation for entered values built into the database, you should do it in a stored procedure or a trigger.