有两个日期字段 StartDate EndDate
2008-03-01 19:30:00 2008-12- 12 08:30:30
意思是自2008-03-01 至 2008-12- 12 每天19:30-8:30 之间合法
问题 1:
怎么判断2008 06 21 18:30 是不是合法? 问题2:
StartDate EndDate
2008-03-01 8:30:00 2008-12- 12 19:30:30
意思是自2008-03-01 至 2008-12- 12 每天8:30-19:30 之间合法 怎么判断2008 06 21 18:30 是不是合法?
SELECT ISDATE('2008 06 21 18:30')
/*
0
*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([StartDate] datetime,[EndDate] datetime)
insert [tb]
select '2008-03-01 08:30:00','2008-12-12 19:30:30' declare @date datetime
set @date='2008-06-21 18:30'
if exists(select *
from tb
where @date between startdate and enddate
and (convert(char(8),@date,108) between convert(char(8),StartDate,108) and convert(char(8),EndDate,108)) or convert(char(8),@date,108) between convert(char(8),EndDate,108) and convert(char(8),StartDate,108)))
print '合法'
else
print '不合法' 这个不行吗?
insert into @t select '2008-03-01 19:30:00','2008-12- 12 08:30:30'
declare @d datetime
set @d='2008-06-21 18:30'
select case when @d between
dateadd(d,datediff(d,d1,@d),d1) and dateadd(d,datediff(d,d2,@d),d2)
then '合法' else '非法' end
from @t
insert into @t select '2008-03-01 08:30:00','2008-12- 12 19:30:30'
declare @d datetime
set @d='2008-06-21 18:30'
select case when @d between
dateadd(d,datediff(d,d1,@d),d1) and dateadd(d,datediff(d,d2,@d),d2)
then '合法' else '非法' end
from @t--合法
把时间段归为同一日就行了
DECLARE @StartDate DATETIME, @EndDate DATETIME, @DATE DATETIMESET @StartDate='2008-03-01 19:30'
SET @EndDate='2008-12-12 8:30'
SET @DATE='2008-06-21 18:30'IF (@StartDate<=@DATE AND @DATE<=@EndDate)
PRINT 'OK' ELSE
PRINT 'NO'
问题1 和问题2 是两种情况
问题1 跨一天了 导致开始时间比结束时间小,但日期大,
问题2 同一天 开始时间比结束时间小要在同一个存储过程中解决问题1 和问题2的情况
insert into @t select '2008-03-01 19:30:00','2008-12- 12 08:30:30'
declare @d datetime
set @d='2008-06-21 18:30'
select dateadd(d,datediff(d,d1,@d),d1),
case when dateadd(d,datediff(d,d2,@d),d2)<dateadd(d,datediff(d,d1,@d),d1) then dateadd(d,datediff(d,d2,@d),d2)+1
else dateadd(d,datediff(d,d2,@d),d2) end from @t/*
2008-06-21 19:30:00.000 2008-06-22 08:30:30.000
*/
时间的话可以统一折算成分钟来进行判断
set @date='2009-05-18 23:01:01'
select * from schedule where
convert(CHAR(8),@DATE,108) between
( case when convert(char(8),startdate,108)>convert(char(8),enddate,108) then
((convert(char(8),startdate,108) and convert(char(8),'23:59:59',108)) or(
convert(char(8),'00:00:00',108) and convert(char(8),enddate,108)))
else (convert(char(8),startdate,108) and convert(char(8),enddate,108)) end)这样成吗?