Prevent SQL from allowing overlapping date ranges in table without using trigger

You could use a check constraint to do this, but I wouldn't recommend it. Triggers or logic in the data layer (stored procedures or sql code) would be better.

I'm away from an instance at present so can't knock one together, but I'll post later.

Edit : time to add some example code

 IF EXISTS(SELECT id FROM sys.sysobjects WHERE type = 'U' AND name = N'ValueRange')
     DROP TABLE ValueRange

 CREATE TABLE ValueRange(
    Name char(3) not null,
    Value int not null,
    FromDate datetime not null,
    ToDate datetime null)
 go
 IF EXISTS(SELECT id FROM sys.sysobjects WHERE type = 'FN' AND name = N'CheckDate')
     DROP FUNCTION CheckDate
 go
 CREATE FUNCTION CheckDate (@Name CHAR(3),@FromDate datetime)
 returns bit
 AS
 begin
 declare @result BIT
 SET @result = 0
 IF (SELECT COUNT(*) FROM ValueRange 
     WHERE NAME = @Name 
     AND @FromDate >= Fromdate
     AND @FromDate <= Todate) > 1
     SET @result = 1

 RETURN @result
 end
 go
 ALTER TABLE dbo.ValueRange
 ADD CONSTRAINT datecheck CHECK (dbo.CheckDate(Name, FromDate) = 0)

   
 INSERT INTO valuerange SELECT 'ABC',12,'1 jan 2000','1 jan 2001'
 INSERT INTO valuerange SELECT 'ABC',13,'1 jul 2000','1 jan 2001'

 SELECT * FROM ValueRange

This code creates a function that returns 1 if there is more than 1 row that meets the criteria - you may need to adjust for the null value of ToDate, but you get the idea. Then add a table check constraint that prevents adding a row where this function returns 1.

Like I said earlier, this isn't the best way, and will perform horrendously, but it fits your (strange) requirement.

I'm presuming that the date range only needs to be checked within the same Name

There is no posibility except triggers or stored procedures as you have mentioned to handle this.
EDIT: As @Kev answered, there is possibility by check constraint.

Anywa It's costly operation especially on bigger tables and you can lower the cost by by creating index on the date columns.

I know this is an oldish thread, but I thought this could only be done by triggers or sprocs until I recently read a Jo Celco article on how to do this with only referential integrity. Unfortunately I can't find the article any more, but IIRC, it went something like this like this:

 CREATE TABLE ValueRange(
    Name char(3) not null,
    Value int not null,
    LastFromDate datetime null, -- LastFromDate is the FromDate of the last previos record for this name/value pair
    FromDate datetime not null,
    ToDate datetime null,
 PRIMARY KEY (Name, Value, FromDate)
 )
 
 --- ensure that LastFromDate points to a valid record
 ALTER TABLE ValueRange ADD CONSTRAINT FK_1 FOREIGN KEY ON (Name, Value, LastFromDate) REFERENCES (Name, Value, FromDate);
 
 --- ensure each record is only referenced by one LastFromDate
 ALTER TABLE ValueRange ADD UNIQUE NONCLUSTERED INDEX IX_1 ON(Name, Value, LastFromDate);
 
 --- Prevent overlapping open-ended date records being added
 ALTER TABLE ValueRange ADD UNIQUE NONCLUSTERED INDEX IX_2 ON(Name, Value) WHERE ToDate is null;
 
 --- Check the date ranges
 ALTER TABLE ValueRange ADD CHECK CONSTRAINT CK_1 AS (ToDate is null or ToDate>FromDate)
 
 ALTER TABLE ValueRange ADD CHECK CONSTRAINT CK_2 AS (LastFromDate is null or LastFromDate<FromDate)

Quite a lot of bits to add, and it does make it hard to insert records, so you still really need a trigger, but a good way to ensure that even data added manually by developers with those pesky triggers turned off is definitely valid.