一些语句优化和面试问题——摘自邹建Sqlserver2000

database 专栏收录该内容
12 篇文章 0 订阅
查当日数据
select * from testdate where DATEDIFF(day,datecol,getdate()) = 0
select * from testdate where datecol >= CONVERT(char(10),getdate(),120) and datecol < CONVERT(char(10),getdate() + 1,120)
查最近2小时数据
select * from testdate where DATEDIFF(hour,datecol,getdate()) between 0 and 2
select * from testdate where datecol between DATEADD(hour, -2,getdate()) and GETDATE()
查指定年月的数据
select * from testdate where YEAR(datecol) = 2013 and MONTH(datecol) = 1
select * from testdate where CONVERT(char(6),datecol,112) = '201301'
select * from testdate where datecol >= '20130101' and datecol < '20130201'
查指定时间段数据
select * from testdate where CONVERT(char(10),datecol,112) between '20130101' and '20130210'
select * from testdate where CONVERT(char(6),datecol,112) between '201301' and '201302'
select * from testdate where datecol >= '20130101' and datecol < '20130211'

select * from testdate where datecol between '20130101' and '20130211'


select REPLACE(convert(char(10),getdate(),120),'-0','-')
select STUFF(stuff(convert(char(8),getdate(),112),5,0,N'年'),8,0,N'月') + N'日'
select DATENAME(year,getdate()) + N'年' + DATENAME(month,getdate()) + N'月' + DATENAME(day,getdate()) + N'日'
select DATENAME(year,getdate()) + N'年' + CAST(datepart(month,getdate()) as varchar) + N'月' + DATENAME(day,getdate()) + N'日'
select CONVERT(char(11),getdate(),120) + CONVERT(char(12),getdate(),114)
select CONVERT(char(4),getdate(),120) + '-1-1'
select CONVERT(char(4),getdate(),120) + '-12-31'

--指定日期所在季度的第一天和最后一天
select CONVERT(datetime,
convert(char(8),
dateadd(month,
datepart(quarter,getdate()) * 3 - 2,
dateadd(month,-month(getdate()),getdate())),
120) + '1')


select CONVERT(datetime,
convert(char(8),
dateadd(month,
datepart(quarter,getdate()) * 3 - MONTH(getdate()) - 2,
getdate()),
120) + '1')


select CONVERT(datetime,
CONVERT(char(8),
dateadd(month,
datepart(quarter,getdate()) * 3,
dateadd(month,-month(getdate()),getdate())),
120)
+ case when datepart(quarter,getdate()) in (1,4)
then '31' else '30' end)


select CONVERT(datetime,
convert(char(8),
dateadd(month,
datepart(quarter,getdate()) * 3 -month(getdate()),
getdate()),
120) 
+ case when datepart(quarter,getdate()) in (1,4) 
then '31' else '30' end)


select DATEADD(day , -1,
convert(char(8),
dateadd(month,datepart(quarter,getdate()) * 3 + 1,
dateadd(month,-month(getdate()),getdate())),
120) + '1') 


select DATEADD(day,-1,
convert(char(8),
dateadd(month,
1 + datepart(quarter,getdate()) * 3 -month(getdate()),
getdate()),
120) + '1')


--指定日期所在月的第一天与最后一天
select CONVERT(datetime,convert(char(8),getdate(),120) + '1')
select DATEADD(day,-1,convert(char(8),DATEADD(MONTH,1,GETDATE()),120) + '1')
--wrong method to get the last day of month
select DATEADD(month,1,dateadd(day,-day(getdate()),getdate()))
--指定日期所在周的第二天
select DATEADD(day,2,dateadd(day,-datepart(weekday,getdate()),getdate()))
select DATEADD(day,2 - datepart(weekday,getdate()),getdate())
--指定日期所在周的星期2
select DATEADD(day,2,
dateadd(day,-(datepart(weekday,getdate()) + @@datefirst - 1) % 7,
getdate()))


select DATEADD(day, 2 - (datepart(weekday,getdate()) + @@DATEFIRST - 1) % 7, GETDATE())
--中国习惯
select DATEADD(day,2 - (datepart(weekday,getdate()) + @@datefirst - 2) % 7 - 1,getdate())





  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 扫一扫,分享海报

参与评论 您还未登录,请先 登录 后发表或查看评论
©️2022 CSDN 皮肤主题:大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值