很多朋友都会遇到这样的问题,怎么获的当前时间是本月的第几周或者第一天改怎么求,在网上搜集了些的资料,拿来给大家共享下。
select left( dateadd(dd,-day(getdate())+1,getdate()) ,112)/*获取当月的第一天*/
Select datediff(week,convert(char(7),getdate(),121)+ '-01 ',getdate())+1 '周'/*获取当月的第几周*/
select yd_datetime=
case when day(yd_datetime)>0 and DAY(yd_datetime)<8 then '第一周,周数1'
when day(yd_datetime)>7 and day(yd_datetime)<15 then '第二周' /*按1-7,8-14,15-21,22-28,的顺序查询周数*/
when DAY(yd_datetime)>14 and DAY(yd_datetime)<22 then '第三周'
when DAY(yd_datetime)>21 and day(yd_datetime)<29 then '第四周'
else '第五周'
end ,id
from t1
select * from t1
case when day(yd_datetime)>0 and DAY(yd_datetime)<8 then '第一周,周数1'
when day(yd_datetime)>7 and day(yd_datetime)<15 then '第二周' /*按1-7,8-14,15-21,22-28,的顺序查询周数*/
when DAY(yd_datetime)>14 and DAY(yd_datetime)<22 then '第三周'
when DAY(yd_datetime)>21 and day(yd_datetime)<29 then '第四周'
else '第五周'
end ,id
from t1
select * from t1
有个朋友问我怎么求时间的间隔,我想了两天才想出来的,感觉MySQL和SQL还是有区别。
create table t1
(
id int,
dt datetime default getdate()
)
insert into t1
(
id int,
dt datetime default getdate()
)
insert into t1
values(1,getdate())
insert into t1
values(2,getdate())
insert into t1
values(2,getdate())
declare @yd datetime
select yd=Datepart(dd,yd) from t2
print yd;
select id, count(id) '次数' ,date_format(yd,'%d') '最近消费时间(号)',left((date_format(yd,'%d')/count(id)),2) '平均间隔' from t2
group by id;
select yd=Datepart(dd,yd) from t2
print yd;
select id, count(id) '次数' ,date_format(yd,'%d') '最近消费时间(号)',left((date_format(yd,'%d')/count(id)),2) '平均间隔' from t2
group by id;
转载于:https://blog.51cto.com/lovetiantian/426374