如何判断每天的时间段

[caixrz]

有两个日期字段 StartDate EndDate
2008-03-01 08:30:00 2008-12- 12 19:30:30




意思是自2008-03-01 08:30:00 至 2008-12- 12 19:30:30 每天8：30-19：30 之间合法

怎么判断2008 06 21 18：30 是不是合法？

[htl258_Tony]

怎么判断2008 06 21 18：30在那个范围内,为什么不合法呢?

[百年树人]

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))
 print '合法'
else 
  print '不合法'

/**

合法

**/

[百年树人]

把时间改为 2008-06-21 20:30 测试

declare @date datetime 
set @date='2008-06-21 20: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))
 print '合法'
else 
  print '不合法'

/**

不合法

**/

[htl258_Tony]

SORRY,看错.我试一下.

[sdhdy]

--用这个取时间
select CONVERT(varchar(12) , getdate(), 108 )
11:06:08

[you_tube]

借用楼上数据
DECLARE @tab table([StartDate] datetime,[EndDate] datetime)
insert  @tab
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 @tab
where  @date  BETWEEN  startdate and enddate
and convert(int,@date,108) >= convert(int,StartDate,108) and convert(int,@date,108) <= convert(int,EndDate,108))
 print '合法'
else 
  print '不合法'

(1 行受影响)
合法

[-狙击手-]

declare @date datetime 
set @date='2008-06-21 18:30'

select *
from tb
where [StartDate] < @date and @date < [EndDate]

[readfuture]

[Quote=引用 3 楼 josy 的回复:]
把时间改为 2008-06-21 20:30 测试
SQL code

declare @date datetime
set @date='2008-06-21 20: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))
print '合法'
else
print '不合法'

/**

不合法

**/


[/Quote]
同意

[ws_hgo]

[Quote=引用楼主 caixrz 的帖子:]
有两个日期字段 StartDate EndDate
2008-03-01 08:30:00 2008-12- 12 19:30:30


意思是自2008-03-01 08:30:00 至 2008-12- 12 19:30:30 每天8：30-19：30 之间合法

怎么判断2008 06 21 18：30 是不是合法？
[/Quote]

declare @StartTime datetime
declare @EndTime datetime
declare @Date datetime
set @Date='2008 06 21 18：30'
set @StartTime ='2008-03-01 08:30:00'
set @EndTime='2008-12- 12 19:30:30'

while Exists (select * from tb where @Date between StartTime and @EndTime)
begin
  print '合法'
end

[dawugui]

[Quote=引用楼主 caixrz 的帖子:]
有两个日期字段 StartDate EndDate
2008-03-01 08:30:00 2008-12- 12 19:30:30


意思是自2008-03-01 08:30:00 至 2008-12- 12 19:30:30 每天8：30-19：30 之间合法

怎么判断2008 06 21 18：30 是不是合法？
[/Quote]

declare @StartDate as datetime
declare @EndDate as datetime
set @StartDate = '2008-03-01 08:30:00'
set @EndDate = '2008-12- 12 19:30:30'

declare @Date as datetime
set @date = '2008-06-21 18:30:00'

if convert(varchar(10),@date,120) between convert(varchar(10),@StartDate,120) and convert(varchar(10),@EndDate,120) and
   convert(varchar(8),@date,114) between convert(varchar(8),@StartDate,114) and convert(varchar(8),@EndDate,114)
   print '合法'
else
   print '不合法'

--result
/*
合法
*/

[ChinaJiaBing]

if OBJECT_ID('pro_j')is not null
   drop procedure pro_j
go
create procedure  pro_j
(@date datetime)
as 
begin 
declare @startDate datetime,@enddate datetime
set @startDate='2008-03-01 08:30:00'
set @enddate='2008-12-12 19:30:30'
if @date>=@startDate and @date<=@enddate
   print '合法'
else 
   print '不合法'
end
go
exec pro_j '2008 06 21 18:30'

----合法

[ChinaJiaBing]

if OBJECT_ID('pro_j')is not null
   drop procedure pro_j
go
create procedure  pro_j
(@date datetime)
as 
begin 
declare @startDate datetime,@enddate datetime
set @startDate='2008-03-01 08:30:00'
set @enddate='2008-12-12 19:30:30'
if @date>=@startDate and @date<=@enddate
   print '合法'
else 
   print '不合法'
end
go
exec pro_j '2008 06 21 18:30'

----合法

[htl258_Tony]

-->借用DAWUGUI的代码:
declare @StartDate as datetime
declare @EndDate as datetime
set @StartDate = '2008-03-01 08:30:00'
set @EndDate = '2008-12- 12 19:30:30'

declare @Date as datetime
set @date = '2008-06-21 18:30:00'

if datediff(day,0,@date) between datediff(day,0,@StartDate)  and datediff(day,0,@EndDate) and 
   dateadd(day,-datediff(day,0,@Date),@Date) between dateadd(day,-datediff(day,0,@StartDate),@StartDate) and dateadd(day,-datediff(day,0,@EndDate),@EndDate)
   print '合法'
else
   print '不合法'

--result
/*
合法
*/

[htl258_Tony]

[Quote=引用 12 楼 ChinaJiaBing 的回复:]
SQL code

if OBJECT_ID('pro_j')is not null
drop procedure pro_j
go
create procedure pro_j
(@date datetime)
as
begin
declare @startDate datetime,@enddate datetime
set @startDate='2008-03-01 08:30:00'
set @enddate='2008-12-12 19:30:30'
if @date>=@startDate and @date<=@enddate
print '合法'
else
print '不合法'
end
go
exec pro_j '2008 06 21 18:30'

----合法
[/Quote]exec pro_j '2008 06 21 21:30' 还是合法,所以这是不可取的.

[lovezx1028]

收藏加学习啦