SQLServer中对时间和长度的处理

---关于时间处理的函数

GETDATE()
DATEADD(DAY,-20,GETDATE())
CONVERT(VARCHAR(100), GETDATE(), 112)
DATENAME(PARAM,DATE);


SELECT GETDATE()
SELECT DATEADD(DAY,-20,GETDATE())
SELECT CONVERT(VARCHAR(100), GETDATE(), 112)
SELECT DATENAME(MONTH,GETDATE());
SELECT DATENAME(MONTH,CONVERT(VARCHAR(100), GETDATE(), 112));

-------详细释义------------
DATEADD() 函数在日期中添加或减去指定的时间间隔。
DATE 参数是合法的日期表达式。NUMBER 是您希望添加的间隔数；对于未来的时间，此数是正数，对于过去的时间，此数是负数。
/*DATEPART 参数可以是下列的值：
DATEPART缩写年YY, YYYY季度QQ, Q月MM, M年中的日DY, Y日DD, D周WK, WW星期DW, W小时HH分钟MI, N秒SS, S毫秒MS微妙MCS纳秒NS
DATEADD(DATEPART,NUMBER,DATE)*/
DATEPART: 
YEAR
MONTH
DAY
WEEK
QUARTER

NUMBER: 添加的间隔数
正数(未来)
负数(过去)

DATE: 合法的日期表达式


SELECT GETDATE()
SELECT DATEADD(DAY,-20,GETDATE())----当前的时间减去20天
SELECT DATEADD(MONTH,-1,GETDATE())----当前的时间减去1个月
SELECT DATEADD(YEAR,-1,GETDATE())----当前的时间减去1年
SELECT DATEADD(WEEK,-1,GETDATE())----当前的时间减去7天一周
SELECT DATEADD(QUARTER,-1,GETDATE())----当前的时间减去3个月
SELECT DATEADD(HOUR,-1,GETDATE())----当前的时间减去1小时


SELECT GETDATE() AS TIME UNION
SELECT DATEADD(DAY,-1,GETDATE()) UNION
SELECT DATEADD(MONTH,-1,GETDATE()) UNION
SELECT DATEADD(YEAR,-1,GETDATE()) UNION
SELECT DATEADD(WEEK,-1,GETDATE()) UNION
SELECT DATEADD(QUARTER,-1,GETDATE()) UNION
SELECT DATEADD(HOUR,-1,GETDATE())


SELECT GETDATE()AS TIME UNION
SELECT DATEADD(DD,-1,GETDATE()) UNION
SELECT DATEADD(MM,-1,GETDATE()) UNION
SELECT DATEADD(YY,-1,GETDATE()) UNION
SELECT DATEADD(WW,-1,GETDATE()) UNION
SELECT DATEADD(QQ,-1,GETDATE()) UNION
SELECT DATEADD(HH,-1,GETDATE()) 

SELECT CONVERT(VARCHAR(4), GETDATE(), 0)

SELECT 'CONVERT(VARCHAR(100), GETDATE(), 0)' AS SQL,'   ' AS BLANK, CONVERT(VARCHAR(100), GETDATE(), 0)AS TIME_FORMATE UNION   
SELECT 'CONVERT(VARCHAR(100), GETDATE(), 1)','   ' AS BLANK,CONVERT(VARCHAR(100), GETDATE(), 1) UNION  
SELECT 'CONVERT(VARCHAR(100), GETDATE(), 2)','   ' AS BLANK,CONVERT(VARCHAR(100), GETDATE(), 2) UNION   
SELECT 'CONVERT(VARCHAR(100), GETDATE(), 3)','   ' AS BLANK,CONVERT(VARCHAR(100), GETDATE(), 3) UNION   
SELECT 'CONVERT(VARCHAR(100), GETDATE(), 4)','   ' AS BLANK,CONVERT(VARCHAR(100), GETDATE(), 4) UNION   
SELECT 'CONVERT(VARCHAR(100), GETDATE(), 5)','   ' AS BLANK,CONVERT(VARCHAR(100), GETDATE(), 5) UNION   
SELECT 'CONVERT(VARCHAR(100), GETDATE(), 6)','   ' AS BLANK,CONVERT(VARCHAR(100), GETDATE(), 6) UNION   
SELECT 'CONVERT(VARCHAR(100), GETDATE(), 7)','   ' AS BLANK,CONVERT(VARCHAR(100), GETDATE(), 7) UNION  
SELECT 'CONVERT(VARCHAR(100), GETDATE(), 8)','   ' AS BLANK,CONVERT(VARCHAR(100), GETDATE(), 8) UNION   
SELECT 'CONVERT(VARCHAR(100), GETDATE(), 9)','   ' AS BLANK,CONVERT(VARCHAR(100), GETDATE(), 9) UNION   
SELECT 'CONVERT(VARCHAR(100), GETDATE(), 10)','   ' AS BLANK,CONVERT(VARCHAR(100), GETDATE(), 10) UNION   
SELECT 'CONVERT(VARCHAR(100), GETDATE(), 11)','   ' AS BLANK,CONVERT(VARCHAR(100), GETDATE(), 11) UNION   
SELECT 'CONVERT(VARCHAR(100), GETDATE(), 12)','   ' AS BLANK,CONVERT(VARCHAR(100), GETDATE(), 12) UNION   
SELECT 'CONVERT(VARCHAR(100), GETDATE(), 13)','   ' AS BLANK,CONVERT(VARCHAR(100), GETDATE(), 13) UNION  
SELECT 'CONVERT(VARCHAR(100), GETDATE(), 14)','   ' AS BLANK,CONVERT(VARCHAR(100), GETDATE(), 14)  UNION  
SELECT 'CONVERT(VARCHAR(100), GETDATE(), 20)','   ' AS BLANK,CONVERT(VARCHAR(100), GETDATE(), 20) UNION  
SELECT 'CONVERT(VARCHAR(100), GETDATE(), 21)','   ' AS BLANK,CONVERT(VARCHAR(100), GETDATE(), 21) UNION  
SELECT 'CONVERT(VARCHAR(100), GETDATE(), 22)','   ' AS BLANK,CONVERT(VARCHAR(100), GETDATE(), 22) UNION  
SELECT 'CONVERT(VARCHAR(100), GETDATE(), 23)','   ' AS BLANK,CONVERT(VARCHAR(100), GETDATE(), 23) UNION  
SELECT 'CONVERT(VARCHAR(100), GETDATE(), 24)','   ' AS BLANK,CONVERT(VARCHAR(100), GETDATE(), 24) UNION  
SELECT 'CONVERT(VARCHAR(100), GETDATE(), 25)','   ' AS BLANK,CONVERT(VARCHAR(100), GETDATE(), 25) UNION  
SELECT 'CONVERT(VARCHAR(100), GETDATE(), 100)','   ' AS BLANK,CONVERT(VARCHAR(100), GETDATE(), 100) UNION
SELECT 'CONVERT(VARCHAR(100), GETDATE(), 101)','   ' AS BLANK,CONVERT(VARCHAR(100), GETDATE(), 101) UNION
SELECT 'CONVERT(VARCHAR(100), GETDATE(), 102)','   ' AS BLANK,CONVERT(VARCHAR(100), GETDATE(), 102) UNION
SELECT 'CONVERT(VARCHAR(100), GETDATE(), 103)','   ' AS BLANK,CONVERT(VARCHAR(100), GETDATE(), 103) UNION
SELECT 'CONVERT(VARCHAR(100), GETDATE(), 104)','   ' AS BLANK,CONVERT(VARCHAR(100), GETDATE(), 104) UNION
SELECT 'CONVERT(VARCHAR(100), GETDATE(), 105)','   ' AS BLANK,CONVERT(VARCHAR(100), GETDATE(), 105) UNION
SELECT 'CONVERT(VARCHAR(100), GETDATE(), 106)','   ' AS BLANK,CONVERT(VARCHAR(100), GETDATE(), 106) UNION
SELECT 'CONVERT(VARCHAR(100), GETDATE(), 107)','   ' AS BLANK,CONVERT(VARCHAR(100), GETDATE(), 107) UNION
SELECT 'CONVERT(VARCHAR(100), GETDATE(), 108)','   ' AS BLANK,CONVERT(VARCHAR(100), GETDATE(), 108) UNION
SELECT 'CONVERT(VARCHAR(100), GETDATE(), 109)','   ' AS BLANK,CONVERT(VARCHAR(100), GETDATE(), 109) UNION
SELECT 'CONVERT(VARCHAR(100), GETDATE(), 110)','   ' AS BLANK,CONVERT(VARCHAR(100), GETDATE(), 110) UNION
SELECT 'CONVERT(VARCHAR(100), GETDATE(), 111)','   ' AS BLANK,CONVERT(VARCHAR(100), GETDATE(), 111) UNION
SELECT 'CONVERT(VARCHAR(100), GETDATE(), 112)','   ' AS BLANK,CONVERT(VARCHAR(100), GETDATE(), 112) UNION
SELECT 'CONVERT(VARCHAR(100), GETDATE(), 113)','   ' AS BLANK,CONVERT(VARCHAR(100), GETDATE(), 113) UNION
SELECT 'CONVERT(VARCHAR(100), GETDATE(), 114)','   ' AS BLANK,CONVERT(VARCHAR(100), GETDATE(), 114) UNION
SELECT 'CONVERT(VARCHAR(100), GETDATE(), 120)','   ' AS BLANK,CONVERT(VARCHAR(100), GETDATE(), 120) UNION
SELECT 'CONVERT(VARCHAR(100), GETDATE(), 121)','   ' AS BLANK,CONVERT(VARCHAR(100), GETDATE(), 121) UNION
SELECT 'CONVERT(VARCHAR(100), GETDATE(), 126)','   ' AS BLANK,CONVERT(VARCHAR(100), GETDATE(), 126) UNION
SELECT 'CONVERT(VARCHAR(100), GETDATE(), 130)','   ' AS BLANK,CONVERT(VARCHAR(100), GETDATE(), 130) UNION
SELECT 'CONVERT(VARCHAR(100), GETDATE(), 131)','   ' AS BLANK,CONVERT(VARCHAR(100), GETDATE(), 131)





SELECT YEAR(GETDATE())   ------返回当前时间的年份
SELECT MONTH(GETDATE())  ------返回当前时间的月份
SELECT DAY(GETDATE())    ------返回当前时间的天




SELECT DATENAME(YEAR,GETDATE()) AS 'YEAR'
SELECT DATENAME(MONTH,GETDATE()) AS 'MONTH'
SELECT DATENAME(DAY,GETDATE()) AS 'DD'



------关于长度的处理--------
SELECT LEN('ABCDEFGHIJKLMNOPQRSTUVWXYZ') --26
SELECT LEN('ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ')  ---26+26=52
SELECT LEN('人生得意须尽欢莫使金樽空对月潮平两岸阔风正一帆悬人生得意须尽欢莫使金樽空对月')  ---38
SELECT LEN('山随平野尽江入大荒流浮云游子意落日故人情白发三千丈缘愁似个长床前明月光疑是地上霜')  ----40个汉字
SELECT LEN ('CSTNAME不管中英文空格一共40个 CSTNAME不管中英文空格 一共40个')-------40
SELECT LEN ('床前明月光疑是地上霜山随平野尽江入大荒流浮云游子意落日故人情白发三千丈缘愁似个长床前明月光疑是地上霜')
---50
SELECT LEN ('紫阁连终南青冥天倪色凭崖望咸阳宫阙罗北极万井惊画出九衢如弦直渭水银河清横天流不息朝野盛文物衣冠何翕赩厩马散连山军容威绝域伊皋运元化卫霍输筋力歌钟乐未休荣去老还逼圆光过满缺太阳移中昃不散东海金何争西飞匿无作牛山悲恻怆泪沾臆')
---110
SELECT LEN ('紫阁连终南青冥天倪色凭崖望咸阳宫阙罗北极万井惊画出九衢如弦直渭水银河清横天流不息朝野盛文物衣冠何翕赩厩马散连山军容威绝域伊皋运元化卫霍输筋力歌钟乐未休荣去老还逼圆光过满缺太阳移中昃不散东海金何争西飞匿')
---100

SELECT DATEADD(MONTH,1,GETDATE()) ----当前时间的下个月时间取出来
SELECT DATENAME(MONTH,DATEADD(MONTH,1,GETDATE()))+'01' -------当前时间的下个月时间一号取出来
SELECT DATENAME(YEAR,DATEADD(MONTH,1,GETDATE()))  ----当前时间的下个月对应的年取出来
SELECT DATEADD(DAY,-1,DATENAME(YEAR,DATEADD(MONTH,1,GETDATE()))+DATENAME(MONTH,DATEADD(MONTH,1,GETDATE()))+'01')  ----当前时间的月底
SELECT CAST(DATEADD(DAY,-1,DATENAME(YEAR,DATEADD(MONTH,1,GETDATE()))+DATENAME(MONTH,DATEADD(MONTH,1,GETDATE()))+'01') AS DATE)---当前时间的月底--强制转化成时间格式
SELECT DATEADD(YEAR,1,CAST(DATEADD(DAY,-1,DATENAME(YEAR,DATEADD(MONTH,1,GETDATE()))+DATENAME(MONTH,DATEADD(MONTH,1,GETDATE()))+'01') AS DATE))---在上面基础上增加一年


SELECT DATEADD(MONTH,1,'20161201') ----当前时间的下个月时间取出来
SELECT DATENAME(MONTH,DATEADD(MONTH,1,'20161201'))+'01' -------当前时间的下个月时间一号取出来
SELECT DATENAME(YEAR,DATEADD(MONTH,1,'20161201'))  ----当前时间的下个月对应的年取出来
SELECT DATEADD(DAY,-1,DATENAME(YEAR,DATEADD(MONTH,1,'20161201'))+DATENAME(MONTH,DATEADD(MONTH,1,'20161201'))+'01')  ----当前时间的月底
SELECT CAST(DATEADD(DAY,-1,DATENAME(YEAR,DATEADD(MONTH,1,'20161201'))+DATENAME(MONTH,DATEADD(MONTH,1,'20161201'))+'01') AS DATE)---当前时间的月底--强制转化成时间格式
SELECT DATEADD(YEAR,1,CAST(DATEADD(DAY,-1,DATENAME(YEAR,DATEADD(MONTH,1,'20161201'))+DATENAME(MONTH,DATEADD(MONTH,1,'20161201'))+'01') AS DATE))---在上面基础上增加一年