获取指定日期所在月的最小天的数据。表A如下
id date(datetime类型) name count
1 2011-2-1 00:00:00 1 1
2 2011-2-1 00:00:00 2 2
3 2011-2-1 00:00:00 3 3
4 2011-2-2 00:00:00 1 1
5 2011-2-2 00:00:00 1 1
6 2011-2-3 00:00:00 1 1
7 2011-2-3 00:00:00 1 18 2011-3-2 00:00:00 1 1
9 2011-3-2 00:00:00 1 1
10 2011-3-3 00:00:00 1 1
11 2011-3-3 00:00:00 1 1比如输入2011-2-12 那么获取2011年2月最小日的数据
应该是1号的数据
1 2011-2-1 00:00:00 1 1
2 2011-2-1 00:00:00 2 2
3 2011-2-1 00:00:00 3 3如果输入2011-3-18,那么获取2011年3月最小日的数据
应该是2号的数据(1号没数据)
8 2011-3-2 00:00:00 1 1
9 2011-3-2 00:00:00 1 1
id date(datetime类型) name count
1 2011-2-1 00:00:00 1 1
2 2011-2-1 00:00:00 2 2
3 2011-2-1 00:00:00 3 3
4 2011-2-2 00:00:00 1 1
5 2011-2-2 00:00:00 1 1
6 2011-2-3 00:00:00 1 1
7 2011-2-3 00:00:00 1 18 2011-3-2 00:00:00 1 1
9 2011-3-2 00:00:00 1 1
10 2011-3-3 00:00:00 1 1
11 2011-3-3 00:00:00 1 1比如输入2011-2-12 那么获取2011年2月最小日的数据
应该是1号的数据
1 2011-2-1 00:00:00 1 1
2 2011-2-1 00:00:00 2 2
3 2011-2-1 00:00:00 3 3如果输入2011-3-18,那么获取2011年3月最小日的数据
应该是2号的数据(1号没数据)
8 2011-3-2 00:00:00 1 1
9 2011-3-2 00:00:00 1 1
go
--> -->
if not object_id(N'Tempdb..#A') is null
drop table #A
Go
Create table #A([id] int,[date] Datetime,[name] int,[count] int)
Insert #A
select 1,'2011-2-1',1,1 union all
select 2,'2011-2-1',2,2 union all
select 3,'2011-2-1',3,3 union all
select 4,'2011-2-2',1,1 union all
select 5,'2011-2-2',1,1 union all
select 6,'2011-2-3',1,1 union all
select 7,'2011-2-3',1,1 union all
select 8,'2011-3-2',1,1 union all
select 9,'2011-3-2',1,1 union all
select 10,'2011-3-3',1,1 union all
select 11,'2011-3-3',1,1
Go
declare @Dt datetime
set @Dt='2011-2-12'
select
a.*
from #A as a
inner join (Select Min([date])as [date] from #A where datediff(mm,@Dt,[date])=0)b on a.[date]=b.[date]
;with maco as
(
select * from 表A where convert(varchar(7),date,120)=convert(varchar(7),@p,120)
)
select * from maco a where date=(select min(date) from maco)
set @Dt='2011-2-12'
select *
from
(Select Min([date]) over() as [Mindate],* from #A where datediff(mm,@Dt,[date])=0)t
where [date]=[Mindate]
declare @Dt datetime
set @Dt='2011-2-12'select *
from tb t
where date <= @Dt
and not exists (select 1 from tb where date <= @Dt and date > t.date)
select min(date) from tb where datepart(mm,date)=@date
group by datepart(mm,date)
select * from tab_name where day(date)
in(
select min(day(date))
from tab_name
where month(date)=month(@dt)
) and month(date)=month(@dt)
insert into @T
select 1,'2011-2-1 00:00:00', '1', 1 union all
select 2,'2011-2-1 00:00:00', '2', 2 union all
select 3, '2011-2-1 00:00:00', '3', 3 union all
select 4, '2011-2-2 00:00:00', '1', 1 union all
select 5, '2011-2-2 00:00:00', '1', 1 union all
select 6, '2011-2-3 00:00:00', '1', 1 union all
select 7, '2011-2-3 00:00:00', '1', 1 union all
select 8, '2011-3-2 00:00:00', '1', 1 union all
select 9, '2011-3-2 00:00:00', '1', 1 union all
select 10, '2011-3-3 00:00:00', '1', 1 union all
select 11, '2011-3-3 00:00:00', '1', 1declare @d datetime
set @d='2011-3-18'
;with a as
(
select * from @T where year(tDate)=year(@d) and month(tDate)=month(@d)
)select id,tDate, uname,tcount from a where tDate in(select min(Convert(datetime,tdate)) as tDate from a)
--8 2011-03-02 00:00:00.000 1 1
--9 2011-03-02 00:00:00.000 1 1