当前位置：首页 → 问答吧 → 求一个关于日期的SQL语句

求一个关于日期的SQL语句

时间：2011-12-03

来源：互联网

求一条SQL文

取得一个月内的数据，例如有字段id,name,datetime
要求参数为日期，比方说输入25，则查出上月25号到今天在记录

求高手指点。。。谢谢。。。

作者: al525la 发布时间: 2011-12-03

select 查询字段 from 表 where 日期 between ………… and …………

作者: hanmengyuan 发布时间: 2011-12-03

SQL code

select * from tb
where [datetime] between convert(varchar(8),dateadd(m,1,getdate()),120)+'25' and getdate()

作者: qianjin036a 发布时间: 2011-12-03

SQL code


select * from tb where [datetime] between dateadd(dd,24,dateadd(mm,-1,dateadd(dd,-day(getdate())+1,getdate()))) and getdate()

作者: pengxuan 发布时间: 2011-12-03

引用 2 楼 qianjin036a 的回复:
SQL code
select * from tb
where [datetime] between convert(varchar(8),dateadd(m,1,getdate()),120)+'25' and getdate()

试了不对啊

作者: al525la 发布时间: 2011-12-03

引用 4 楼 al525la 的回复:

引用 2 楼 qianjin036a 的回复:
SQL code
select * from tb
where [datetime] between convert(varchar(8),dateadd(m,1,getdate()),120)+'25' and getdate()

试了不对啊

抱歉,在 dateadd 函数里少打一个负号:
SQL code

create table tb(id int,name varchar(10),[datetime] datetime)
insert into tb select 1,'aa','2011-11-5'
insert into tb select 1,'bb','2011-11-26'
insert into tb select 1,'cc','2011-12-2'
go
select * from tb
where [datetime] between convert(varchar(8),dateadd(m,-1,getdate()),120)+'25' and getdate()
/*
id          name       datetime
----------- ---------- -----------------------
1           bb         2011-11-26 00:00:00.000
1           cc         2011-12-02 00:00:00.000

(2 行受影响)

*/
go
drop table tb

作者: qianjin036a 发布时间: 2011-12-03

SQL code


if object_id('tb','U') is not null
   drop table tb
go
create table tb
(
 id int,
 [datetime] datetime
)
insert into tb
select 1,'2011-11-1' union all
select 1,'2011-11-2' union all
select 1,'2011-11-3' union all
select 1,'2011-11-11' union all
select 1,'2011-11-12' union all
select 1,'2011-11-13' union all
select 1,'2011-11-25' union all
select 1,'2011-11-26' union all
select 1,'2011-11-27' union all
select 1,'2011-12-1' union all
select 1,'2011-12-2' union all
select 1,'2011-12-3'
go
select * from tb where [datetime] between convert(varchar(10),dateadd(dd,24,dateadd(mm,-1,dateadd(dd,-day(getdate())+1,getdate()))),120) and getdate()
/*
id          datetime
----------- -----------------------
1           2011-11-25 00:00:00.000
1           2011-11-26 00:00:00.000
1           2011-11-27 00:00:00.000
1           2011-12-01 00:00:00.000
1           2011-12-02 00:00:00.000
1           2011-12-03 00:00:00.000

(6 行受影响)

*/

作者: pengxuan 发布时间: 2011-12-03

求一个关于日期的SQL语句

热门阅读

热门下载