date check - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

date check

Author

Topic

jamsman
Starting Member

3 Posts

Posted - 2005-03-04 : 10:45:16

i am trying to create a table that has a date in it i need to check that the date is greater then or equal the currnet date how do i do this

James

cshah1
Constraint Violating Yak Guru

347 Posts

Posted - 2005-03-04 : 10:57:23

DATEDIFF
Returns the number of date and time boundaries crossed between two specified dates.

Syntax
DATEDIFF ( datepart , startdate , enddate )

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-04 : 10:59:00

I presume old data can have dates older than current date.

-- on new table
create table #t(d datetime check( d>=getdate()))

-- on existing table
alter table t with nocheck add constraint chkDate check(d>=getdate())

drop table t

rockmoose

X002548
Not Just a Number

15586 Posts

Posted - 2005-03-04 : 11:04:18

EDIT: Dooh

But you probably want to incorporate datediff to eliminate the tome factor.


USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myTable99(Col1 int IDENTITY(1,1)
		, myDate datetime CHECK(DATEDIFF(dd,GetDate(),myDate)>=0))
GO

INSERT INTO myTable99(myDate) SELECT '1/1/9999'
GO

SELECT * FROM myTable99
GO

INSERT INTO myTable99(myDate) SELECT '1/1/1900'
GO

SET NOCOUNT OFF
DROP TABLE myTable99
GO

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources