【网学网提醒】：网学会员鉴于大家对十分关注，论文会员在此为大家搜集整理了“SQL时间处理”一文，供大家参考学习
SELECTCONVERT(VARCHAR(10),DATEADD(mm,DATEDIFF(mm,0,getdate()),0),120)
SELECTCONVERT(VARCHAR(10),DATEADD(ms,-3,DATEADD(mm,DATEDIFF(m,0,getdate())+1,0)),120)
---上个月月初第一天
selectCONVERT(varchar(12),DATEADD(mm,DATEDIFF(mm,0,dateadd(mm,-1,getdate())),0),112)
---上个月月末最后一天
selectCONVERT(varchar(12),dateadd(ms,-3,DATEADD(mm,DATEDIFF(m,0,getdate()),0)),112)
1.显示本月第一天
SELECTDATEADD(mm,DATEDIFF(mm,0,getdate()),0)selectconvert(datetime,convert(varchar(8),getdate(),120)+'01',120)
2.显示本月最后一天
selectdateadd(day,-1,convert(datetime,convert(varchar(8),dateadd(month,1,getdate()),120)+'01',120))SELECTdateadd(ms,-3,DATEADD(mm,DATEDIFF(m,0,getdate())+1,0))
3.上个月的最后一天
SELECTdateadd(ms,-3,DATEADD(mm,DATEDIFF(mm,0,getdate()),0))
4.本月的第一个星期一i
selectDATEADD(wk,DATEDIFF(wk,0,dateadd(dd,6-datepart(day,getdate()),getdate())),0)
5.本年的第一天
SELECTDATEADD(yy,DATEDIFF(yy,0,getdate()),0)
6.本年的最后一天
SELECTdateadd(ms,-3,DATEADD(yy,DATEDIFF(yy,0,getdate())+1,0))
7.去年的最后一天
SELECTdateadd(ms,-3,DATEADD(yy,DATEDIFF(yy,0,getdate()),0))
8.本季度的第一天
SELECTDATEADD(qq,DATEDIFF(qq,0,getdate()),0)
9.本周的星期一
SELECTDATEADD(wk,DATEDIFF(wk,0,getdate()),0)
10.查询本月的记录
select*fromtableNamewhereDATEPART(mm,theDate)=DATEPART(mm,GETDATE())andDATEPART(yy,theDate)=DATEPART(yy,GETDATE())
11查询本周的记录
select*fromtableNamewhereDATEPART(wk,theDate)=DATEPART(wk,GETDATE())andDATEPART(yy,theDate)=DATEPART(yy,GETDATE())
12查询本季的记录注：其中:GETDATE()是获得系统时间的函数。
select*fromtableNamewhereDATEPART(qq,theDate)=DATEPART(qq,GETDATE())andDATEPART(yy,theDate)=DATEPART(yy,GETDATE())
13.获取当月总天数:
selectDATEDIFF(dd,getdate(),DATEADD(mm,1,getdate()))selectdatediff(day,dateadd(mm,datediff(mm,'',getdate()),''),dateadd(mm,datediff(mm,'',getdate()),'1900-02-01'))
14.获取当前为星期几
DATENAME(weekday,getdate())
-------------------------------------------------------------------------------------------
1.selectCONVERT(varchar(12),getdate(),101)
12/28/2007
2.selectCONVERT(varchar(12),getdate(),102)
2007.12.28
3.selectCONVERT(varchar(12),getdate(),103)
28/12/2007
4.selectCONVERT(varchar(12),getdate(),104)
28.12.2007
5.selectCONVERT(varchar(12),getdate(),105)
28-12-2007
6.selectCONVERT(varchar(12),getdate(),106)
28122007
7.selectCONVERT(varchar(12),getdate(),107)
1228,2007
8.selectCONVERT(varchar(12),getdate(),108)
10:06:33
9.selectCONVERT(varchar(12),getdate(),109)
122820071
10.selectCONVERT(varchar(12),getdate(),110)
12-28-2007
11.selectCONVERT(varchar(12),getdate(
),111)
2007/12/28
12.selectCONVERT(varchar(12),getdate(),112)
20071228
13.selectCONVERT(varchar(12),getdate(),113)
281220071
14.selectCONVERT(varchar(12),getdate(),114)
10:07:36:623
15.selectCONVERT(varchar,getdate(),120)
2007-12-2810:08:56
16.selectreplace(replace(replace(CONVERT(varchar,getdate(),120),'-',''),'',''),':','')
20071228101012
