把日期和时间换为变量即可----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2014-02-07 12:54:25
-- 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-07 9:20:02' union all
select 2,'2014-01-08 9:20:02' union all
select 3,'2014-01-24 16:20:02'
--------------开始查询--------------------------select * from [tableC]
WHERE CONVERT(VARCHAR(10),[LogTime],120) BETWEEN '2014-01-02' AND '2014-01-24'
AND CONVERT(VARCHAR(10),[LogTime],8) BETWEEN '09:00:00.000' AND '10:00:00.000'
----------------结果----------------------------
/*
Id LogTime
----------- -----------------------
1 2014-01-07 09:20:02.000
2 2014-01-08 09:20:02.000
*/
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2014-02-07 12:54:25
-- 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-07 9:20:02' union all
select 2,'2014-01-08 9:20:02' union all
select 3,'2014-01-24 16:20:02'
--------------开始查询--------------------------select * from [tableC]
WHERE CONVERT(VARCHAR(10),[LogTime],120) BETWEEN '2014-01-02' AND '2014-01-24'
AND CONVERT(VARCHAR(10),[LogTime],8) BETWEEN '09:00:00.000' AND '10:00:00.000'
----------------结果----------------------------
/*
Id LogTime
----------- -----------------------
1 2014-01-07 09:20:02.000
2 2014-01-08 09:20:02.000
*/
WHERE CONVERT(date,[LogTime]) BETWEEN '2014-01-02' AND '2014-01-24'
AND CONVERT(time,[LogTime]) BETWEEN '09:00:00.000' AND '10:00:00.000'
我试了一下,奇怪,不起作用,是不是因为sql server版本的问题?
我用的是sql server 2000
我试了一下,奇怪,不起作用,是不是因为sql server版本的问题?
我用的是sql server 2000不起作用是什么意思?我这个2000应该是没问题的
我试了一下,奇怪,不起作用,是不是因为sql server版本的问题?
我用的是sql server 2000不起作用是什么意思?我这个2000应该是没问题的原因找到了,
CONVERT(VARCHAR(10),[LogTime],8) BETWEEN '09:00:00.000' AND '10:00:00.000'
这句中的9点前我忘了加0了