SQL Server时间算法大全

SQLServer时间算法大全2010-09-2716:19我要评论(0)字号：T|TSQLServerSQL有更多的认识。AD： 2013PPTSQLServer时间时期方面的计算问题进行了整合集中，如果您对SQLServerSQLServer1: DECLARE@Date DATETIME2:2:SET@Date=GETDATE()3:--前一天，给定日期的前一天4:SELECTDATEADD(DAY,-1,@Date)AS'前一天'5:--后一天，给定日期的后一天6:SELECTDATEADD(DAY,1,@Date)AS'后一天'7:GO8:9:10:--月初，计算给定日期所在月的第一天11:--这个计算的技巧是先计算当前日期到“1900-01-01”的时间间隔数，然后把它加到“1900-01-01”上来获得特殊的日期，这个技巧可以用---来计算很多不同的日期。12:DECLARE@DateDATETIME13:SET@Date=GETDATE()14:SELECTDATEADD(MONTH,DATEDIFF(MONTH,'1900-01-01',@Date),'1900-01-01')AS'所在月的第一天'15:--SQLServer的时间表示方式可知，'1900-01-010代替16:SELECTDATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0)AS'所在月的第一天'17:--上面两种算法精确到天时分秒均为00:00:00.00018:--下面算法课以保留时分秒19:--思路：用给定日期减去月第一天与给定日期差的天数20:SELECTDATEADD(DAY,1-DATEPART(DAY,@Date),@Date)21:GO22:23:--月末，计算给定日期所在月的最后一天24:DECLARE@DateDATETIME25:SET@Date=GETDATE()26:--11天27:SELECTDATEADD(DAY,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,'1900-01-01',@Date),'1900-01-01'))AS'所在月的最一天'28:SELECTDATEADD(MONTH,1+DATEDIFF(MONTH,'1900-01-01',@Date),'1900-01-01')-1AS'所在月的最一天'29:--1900-01-010代替30:SELECTDATEADD(DAY,-1,DATEADD(MONTH,1+DATEDIFF(MONTH,0,@Date),0))AS'所在月的最一天'31:SELECTDATEADD(MONTH,1+DATEDIFF(MONTH,0,@Date),0)-1AS'所在月的最一天'32:--思路：与月初计算思路相同33:SELECTDATEADD(MONTH,DATEDIFF(MONTH,'1989-12-31',@Date),'1989-12-31')AS'所在月的最一天'34:--精简算法，'1989-12-31用-1代替35:SELECTDATEADD(MONTH,DATEDIFF(MONTH,-1,@Date),-1)AS'所在月的最一天'36:--保留时分秒的算法37:SELECTDATEADD(DAY,-1,DATEADD(MONTH,1,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date)))38:GO39:40:--其他月计算41:42:--计算给定日期所在月的上月第一天43:DECLARE@DateDATETIME44:SET@Date=GETDATE()45:--当前月第一天减去一个月46:SELECTDATEADD(MONTH,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0))AS'上月第一天'47:--简化48:SELECTDATEADD(MONTH,DATEDIFF(MONTH,0,@Date)-1,0)AS'上月第一天'49:--另一种当前月第一天算法50:SELECTDATEADD(MONTH,-1,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date))'上月第一天'51:GO52:53:--计算给定日期所在月的上月最后一天54:DECLARE@DateDATETIME55:SET@Date=GETDATE()56:--当前月第一天减去一天57:SELECTDATEADD(DAY,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0)AS'上月最后一天'58:--另一种当前月第一天算法59:SELECTDATEADD(DAY,-1,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date))'上月最后一天'60:SELECTDATEADD(DAY,1-DATEPART(DAY,@Date),@Date)-1'上月最后一天'61:--30天。62:--SELECTDATEADD(MONTH,1,'2010-06-30'2010-07-30而不是2010-07-31，63:--1天计算的原因64:--31天择无此问题65:--例如SELECTDATEADD(MONTH,1,'2010-05-31')--结果是2010-06-3066:--因此下面算法是正确的，-1表示'1899-12-3100:00:00.000'--SELECTCONVERT(DATETIME,-1)67:SELECTDATEADD(MONTH,DATEDIFF(MONTH,-1,@Date)-1,-1)68:--另一种当前月算法69:SELECTDATEADD(DAY,-1,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date))'上月最后一天'70:--简化71:SELECTDATEADD(DAY,0-DATEPART(DAY,@Date),@Date)'上月最后一天'72:GO73:74:--计算给定日期所在月的下月第一天75:DECLARE@DateDATETIME76:SET@Date=GETDATE()77:--当前月第一天加一个月78:SELECTDATEADD(MONTH,1,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0))AS'下月第一天'79:80:--简化SELECTDATEADD(MONTH,DATEDIFF(MONTH,0,@Date)+1,0)AS'下月第一天'81:82:--另一种当前月第一天算法SELECTDATEADD(MONTH,1,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date))'下月第一天'83:GO84:85:--计算给定日期所在月的下月最后一天86:DECLARE@DateDATETIME87:SET@Date=GETDATE()88:--21天89:SELECTDATEADD(DAY,-1,DATEADD(MONTH,2,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0)))AS'下月最后一天'90:--简化91:SELECTDATEADD(DAY,-1,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date)+2,0))AS'下月最后一天'92:SELECTDATEADD(MONTH,DATEDIFF(MONTH,0,@Date)+2,0)-1AS'下月最后一天'93:--另一种算法94:SELECTDATEADD(MONTH,DATEDIFF(MONTH,-1,@Date)+1,-1)'下月最后一天'95:--另一种当前月第一天算法96:SELECTDATEADD(DAY,-1,DATEADD(MONTH,2,DATEADD(DAY,1-DATEPART(DAY,@Date),@Date)))'下月最后一天'97:GO98:99:--1天(星期日为第一天)100:DECLARE@DateDATETIME101:SET@Date=GETDATE()102:--SQLServer语言版本相关的算法103:--思路：当前日期+星期日(1天)与当前日期的差的天数104:--DATEPART(WEEKDAY,DATE)的返回值与@@DATEFIRST相关105:SETDATEFIRST7--或者设置为美国英语SETLANGUAGEus_english;(星期日为第一天)106:SELECTDATEADD(WEEKDAY,1-DATEPART(WEEKDAY,@Date),@Date)AS'所在星期的第一天，星期日'107:--SQLServer语言版本或@@DATEFIRST无关108:--'1989-12-31是星期日，'1989-12-31再加上(1989-12-31差的星期数)个星期109:SELECTDATEADD(WEEK,DATEDIFF(WEEK,-1,@Date),-1)AS'所在星期的星期日'110:--或者111:SELECTDATEADD(WEEK,DATEDIFF(WEEK,6,@Date),6)AS'所在星期的星期日'112:GO113:114:115:--2天(星期日为第一天)116:DECLARE@DateDATETIME117:SET@Date=GETDATE()118:--思路：当前日期+星期一(2天)与当前日期的差的天数119:--DATEPART(WEEKDAY,DATE)的返回值与@@DATEFIRST相关120:SETDATEFIRST7--或者设置为美国英语SETLANGUAGEus_english;(星期日为第一天)121:SELECTDATEADD(DAY,2-DATEPART(WEEKDAY,@Date),@Date)AS'所在星期的第二天，星期一'122:--SQLServer语言版本或@@DATEFIRST无关123:--'1900-01-01是星期一，'1900-01-01再加上(1900-01-01差的星期数)个星期124:SELECTDATEADD(WEEK,DATEDIFF(WEEK,0,@Date),0)AS'所在星期的星期一'125:GO126:127:--上个星期第一天，计算给定日期所在星期的上一个星期日(星期日为第一天)128:DECLARE@DateDATETIME129:SET@Date=GETDATE()130:--1周131:--DATEPART(WEEKDAY,DATE)的返回值与@@DATEFIRST相关132:SETDATEFIRST7--或者设置为美国英语SETLANGUAGEus_english;(星期日为第一天)133:SELECTDATEADD(WEEK,-1,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date))AS'上个星期第一天，星期日134:--7天135:SELECTDATEADD(DAY,-7,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date))AS'上个星期第一天，星期日'136:--简化137:SELECTDATEADD(DAY,-6-DATEPART(WEEKDAY,@Date),@Date)AS'上个星期第一天，星期日'138:--SQLServer语言版本或@@DATEFIRST无关139:SELECTDATEADD(WEEK,-1+DATEDIFF(WEEK,-1,@Date),-1)AS'上个星期日'140:--或者141:SELECTDATEADD(WEEK,DATEDIFF(WEEK,6,@Date),-1)AS'上个星期日'142:GO143:144:145:--下个星期第一天，计算给定日期所在星期的下一个星期日(星期日为第一天)146:DECLARE@DateDATETIME147:SET@Date=GETDATE()148:--1周149:--DATEPART(WEEKDAY,DATE)的返回值与@@DATEFIRST相关150:SETDATEFIRST7--或者设置为美国英语SETLANGUAGEus_english;(星期日为第一天)151:SELECTDATEADD(WEEK,1,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date))AS'下个星期第一天，星期日152:--7天153:SELECTDATEADD(DAY,7,DATEADD(DAY,1-DATEPART(WEEKDAY,@Date),@Date))AS'下个星期第一天，星期日'154:--简化155:SELECTDATEADD(DAY,8-DATEPART(WEEKDAY,@Date),@Date)AS'下个星期第一天，星期日'156:--SQLServer语言版本或@@DATEFIRST无关157:SELECTDATEADD(WEEK,1+DATEDIFF(WEEK,-1,@Date),-1)AS'下个星期日'158:--或者159:SELECTDATEADD(WEEK,DATEDIFF(WEEK,-1,@Date),6)AS'下个星期日'160:GO161:162:--判断给定日期是星期几163:DECLARE@DateDATETIME164:SET@Date=GETDATE()165:--DATEPART(WEEKDAY,DATE)的返回值与@@DATEFIRST相关166:SETDATEFIRST7--或者设置为美国英语SETLANGUAGEus_english;(星期日为第一天)167:SELECTDATEPART(WEEKDAY,@Date)--返回值星期日，2-星期一，3-星期二 7-星期六168:--上面算法与SQL语言版本或@@DATEFIRST相关169:--SQLServer语言版本或@@DATEFIRST无关170:SELECTDATENAME(WEEKDAY,@Date)'星期'171:GO172:173:174:--年度计算175:DECLARE@DateDATETIME176:SET@Date=GETDATE()177:--年初，计算给定日期所在年的第一天178:SELECTDATEADD(YEAR,DATEDIFF(YEAR,0,@Date),0)AS'所在年的第一天'179:--年末，计算给定日期所在年的最后一天180:SELECTDATEADD(YEAR,DATEDIFF(YEAR,-1,@Date),-1)AS'所在年的最后一天'181:--上一年年初，计算给定日期所在年的上一年的第一天182:SELECTDATEADD(YEAR,DATEDIFF(YEAR,-0,@Date)-1,0)AS'所在年的上一年的第一天'183:--上一年年末，计算给定日期所在年的上一年的最后一天184:SELECTDATEADD(YEAR,DATEDIFF(YEAR,0,@Date),-1)AS'所在年的上一年的最后一天'185:--下一年年初，计算给定日期所在年的下一年的第一天186:SELECTDATEADD(YEAR,1+DATEDIFF(YEAR,0,@Date),0)AS'所在年的下一年的第一天'187:--下一年年末，计算给定日期所在年的下一年的最后一天188:SELECTDATEADD(YEAR,1+DATEDIFF(YEAR,-1,@Date),-1)AS'所在年的下一年的最后一天'189:GO190:191:--季度计算192:DECLARE@DateDATETIME193:SET@Date=GETDATE()194:--季度初，计算给定日期所在季度的第一天195:SELECTDATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date),0)AS'当前季度的第一天'196:--季度末，计算给定日期所在季度的最后一天197:SELECTDATEADD(QUARTER,1+DATEDIFF(QUARTER,0,@Date),-1)AS'当前季度的最后一天'198:--上个季度初199:SELECTDATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date)-1,0)AS'当前季度的上个季度初'200:--上个季度末201:SELECTDATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date),-1)AS'当前季度的上个季度末'202:--下个季度初203:SELECTDATEADD(QUARTER,1+DATEDIFF(QUARTER,0,@Date),0)AS'当前季度的下个季度初'204:--下个季度末205:SELECTDATEADD(QUARTER,2+DATEDIFF(QUARTER,0,@Date),-1)AS'当前季度的下个季度末'206:GO207:208:--计算给定日期所在月的天数209:DECLARE@DateDATETIME;210:SET@Date=GETDATE()211:--本月度第一天与下月度第一天所差的天数212:SELECTDATEDIFF(DAY,DATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0),DATEADD(MONTH,1+DATEDIFF(MONTH,0,@Date),0))213:--借助变量简化214:SELECT@Date=DATEADD(MONTH,DATEDIFF(MONTH,0,@Date),0)--本月度第一天215:SELECTDATEDIFF(DAY,@Date,DATEADD(MONTH,1,@Date))216:--另一种思路：给定月最后一天的日期，记为本月天数217:SELECTDAY(DATEADD(MONTH,DATEDIFF(MONTH,-1,@Date),-1))218:GO219:220:--计算给定日期所在季度的天数221:DECLARE@DateDATETIME;222:SET@Date=GETDATE()223:--本季度第一天与下季度第一天所差的天数224:SELECTDATEDIFF(DAY,DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date),0),DATEADD(QUARTER,1+DATEDIFF(QUARTER,0,@Date),0))225:--借助变量简化226:SELECT@Date=DATEADD(QUARTER,DATEDIFF(QUARTER,0,@Date),0)--本季度第一天227:SELECTDATEDIFF(DAY,@Date,DATEADD(QUARTER,1,@Date))228:GO229:230:--计算给定日期所在年度的天数231:DECLARE@DateDATETIME;232:SET@Date=GETDATE()233:--本年度第一天与下年度第一天所差的天数234:SELECTDATEDIFF(DAY,DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),0),DATEADD(YEAR,1+DATEDIFF(YEAR,0,@Date),0))235:--借助变量简化236:SELECT@Date=DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),0)--本年度第一天237:SELECTDATEDIFF(DAY,@Date,DATEADD(YEAR,1,@Date))238:GO239:240:--判断给定日期所在年是否闰年241:--根据全年总天数判断242:DECLARE@DateDATETIME;243:SET@Date=GETDATE()244:SELECTCASEDATEDIFF(DAY,DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),0),DATEADD(YEAR,1+DATEDIFF(YEAR,0,@Date),0))245: WHEN365THEN'平年'ELSE'闰年END246:--根据二月天数判断247:--22月最后一天248:SELECTCASEDAY(DATEADD(MONTH,2,DATEADD(YEAR,DATEDIFF(YEAR,0,@Date),-1)))WHEN28THEN'平年'ELSE'闰年'END249:GO250:251:--计算给定日期是当年的第几天252:DECLARE@DateDATETIME;253:SET@Date=GETDATE()254:SELECTDATEPART(DAYOFYEAR,@Date)[DayOfYear];255:SELECTDATENAME(DAYOFYEAR,@Date)[DayOfYear];256:--另一种思路：当前日期与上年最后一天差的天数257:SELECT258:GO259:260:--计算给定日期是当年的第几周261:DECLARE@DateDATETIME;262:SET@Date=GETDATE()263:SELECTDATEPART(WEEK,@Date)[WeekOfYear];--返回int型264:SELECTDATENAME(WEEK,@Date[WeekOfYear]varchar型265:GO266:267:--计算给定日期是当年的第几月268:DECLARE@DateDATETIME;269:SET@Date=GETDATE()270:SELECTDATEPART(MONTH,@Date)[MonthOfYear];--返回int型271:SELECTDATENAME(MONTH,@Date[MonthOfYear]varchar型272:SELECTMONTH(@Date[MonthOfYear];--int型273:GO274:275:--计算