34,590
社区成员
发帖
与我相关
我的任务
分享
--字符串格式
if object_id('tempdb..#','U') is not null
drop table #;
go
create table #(qd_date varchar(20), js_date varchar(20))
insert into #
select '2010-3-17','2010-3-20'
select *
from #
where
'2010-03-17' between qd_date and js_date
/*
qd_date js_date
-------------------- --------------------
(0 行受影响)
*/
字符串格式可能存在这样的情况
--时间格式
if object_id('tempdb..#','U') is not null
drop table #;
go
create table #(qd_date datetime, js_date datetime)
insert into #
select '2010-03-17','2010-03-20'
select *
from #
where '2010-03-17' between qd_date and js_date
--字符串格式
if object_id('tempdb..#','U') is not null
drop table #;
go
create table #(qd_date varchar(20), js_date varchar(20))
insert into #
select '2010-03-17','2010-03-20'
select *
from #
where
'2010-03-17' between qd_date and js_date
--结果
/*
(1 行受影响)
qd_date js_date
----------------------- -----------------------
2010-03-17 00:00:00.000 2010-03-20 00:00:00.000
(1 行受影响)
(1 行受影响)
qd_date js_date
-------------------- --------------------
2010-03-17 2010-03-20
(1 行受影响)
*/
--------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-04-09 22:58:22
-- Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:tb
IF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([NO] INT,[qd_date] DATETIME,[js_date] DATETIME)
INSERT [tb]
SELECT 1,N'2010-03-02',N'2010-03-20' UNION ALL
SELECT 2,N'2010-03-22',N'2010-03-25'
GO
--SELECT * FROM [tb]
-->SQL查询如下:
select * from tb where '2010-03-17' between qd_date and js_date
/*
NO qd_date js_date
----------- ----------------------- -----------------------
1 2010-03-02 00:00:00.000 2010-03-20 00:00:00.000
(1 行受影响)
*/
经测试验证,语法不存在问题。可能是你的日期格式是字符型的或其它原因。
where qd_date<='2010-03-17' and js_date >='2010-03-17'