这个语句应该怎么写?

[abz990990]

tableC

Id LogTime
1 2014-01-08 19:48:01
2 2014-01-07 9:40:01
3 2014-01-06 19:02:01

我想查询从2014-01-06到2014-01-08,时间处在00:00:01到08:59:59,并且时间处在18:00:01-23:59:59的数据

最终查询出来的结果是

Id LogTime
1 2014-01-08 19:48:01
3 2014-01-06 19:02:01

请问这个语句应该怎么写?

[abz990990]

这个问题中的两个日期和两个时间段的数据都是动态的

[發糞塗牆]

sqlserver多少的？

[發糞塗牆]

这是2008的

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2014-01-08 17:12:34
-- Version:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
--	Dec 28 2012 20:23:12 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[tableC]
if object_id('[tableC]') is not null drop table [tableC]
go 
create table [tableC]([Id] int,[LogTime] datetime)
insert [tableC]
select 1,'2014-01-08 19:48:01' union all
select 2,'2014-01-07 9:40:01' union all
select 3,'2014-01-06 19:02:01'
--------------开始查询--------------------------

select * from [tableC] a
WHERE CONVERT(DATE, [LogTime]) BETWEEN '2014-01-06' AND '2014-01-08'
AND ((CONVERT(TIME,[LogTime]) BETWEEN '00:00:01' AND '08:59:59' ) OR (CONVERT(TIME,[LogTime]) BETWEEN '18:00:01' AND '23:59:59' ))
----------------结果----------------------------
/* 
Id          LogTime
----------- -----------------------
1           2014-01-08 19:48:01.000
3           2014-01-06 19:02:01.000
*/

[發糞塗牆]

动态的用变量替换就可以了

[hengkangdba]

人工三连断。

[發糞塗牆]

2000~2005通用写法

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2014-01-08 17:12:34
-- Version:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
--	Dec 28 2012 20:23:12 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[tableC]
if object_id('[tableC]') is not null drop table [tableC]
go 
create table [tableC]([Id] int,[LogTime] datetime)
insert [tableC]
select 1,'2014-01-08 19:48:01' union all
select 2,'2014-01-07 9:40:01' union all
select 3,'2014-01-06 19:02:01'
--------------开始查询--------------------------

select * from [tableC] a
WHERE CONVERT(VARCHAR(10), [LogTime],120) BETWEEN '2014-01-06' AND '2014-01-08'
AND ((CONVERT(VARCHAR(10), [LogTime],8) BETWEEN '00:00:01' AND '08:59:59' ) OR (CONVERT(VARCHAR(10), [LogTime],8) BETWEEN '18:00:01' AND '23:59:59' ))
----------------结果----------------------------
/* 
Id          LogTime
----------- -----------------------
1           2014-01-08 19:48:01.000
3           2014-01-06 19:02:01.000
*/

[shoppo0505]

操作时转换成Time类型即可

[shinichi_jtc]

补充一下楼上的，用动态：

declare @startdate date
declare @enddate date
declare @starttime time
declare @endtime time
declare @starttime1 time
declare @endtime1 time

set @startdate='2014-01-06'
set @enddate='2014-01-08'
set @starttime='00:00:01'
set @endtime='08:59:59'
set @starttime1='18:00:01'
set @endtime1='23:59:59'

select * from [tableC] a
WHERE CONVERT(DATE, [LogTime]) BETWEEN @startdate AND @enddate
AND ((CONVERT(TIME,[LogTime]) BETWEEN @starttime AND @endtime ) OR (CONVERT(TIME,[LogTime]) BETWEEN @starttime1 AND @endtime1 ))

现在变量给的固定值，你给需要的值就好了，注意变量类型就好