1、29.SELECT DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1+DATEPART(Quarter,getdate()*3-Month(getdate(),getdate(),120)+1) 30.-季度的最后一天(CASE判断法) 31.select DATEADD(Month,DATEPART(Quarter,getdate()*3-Month(getdate(),getdate() 32. 33.-本月第一个星期一 34.SELECT DATEADD(wk, DATEDIFF(wk, , DATEADD(dd, 6 - DAY(getdat
2、e(), getdate(), 35.-去年最后一天 36.SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate(), 0) 37.-今年第一天 38.SELECT DATEADD(yy, DATEDIFF(yy,0,getdate(), 0) 39.-今年最后一天 40.SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()+1,0) 41. 42.-指定日期所在周的任意一天 43.SELECT DATEADD(Day,number-DATEPART(Weekday,dt),dt
3、)-5指定日期所在周的任意星期几 44.-A. 星期天做为一周的第1天 45.SELECT DATEADD(Day,number-(DATEPART(Weekday,dt)+DATEFIRST-1)%7,dt) 46.-B. 星期一做为一周的第1天 47.SELECT DATEADD(Day,number-(DATEPART(Weekday,dt)+DATEFIRST-2)%7-1,dt) 48.-周内的第几日 49.select datepart(weekday,getdate() as 周内的第几日 50.-年内的第几周 51.select datepart(week,getdate()
4、as 年内的第几周 52.-年内的第几季 53.select datepart(quarter,getdate() as 年内的第几季 54. 55. 56.-判断某天是当月的第几周的sql函数 57.CREATE FUNCTION WeekOfMonth(day datetime) 58.RETURNS int 59.AS 60.begin 61. 62.-declare day datetime 63.declare num int 64.declare Start datetime 65.declare dd int 66.declare dayofweek char(8) 67.dec
5、lare dayofweek_num char(8) 68.declare startWeekDays int 69.-set day=2009-07-0570.if datepart(dd,day)=1 71.return 1 72.else 73.set Start= (SELECT DATEADD(mm, DATEDIFF(mm,0,day), 0) -一个月第一天的 74.set dayofweek= (datename(weekday,Start) -得到本月第一天是周几 75.set dayofweek_num=(select (case dayofweek when 星期一 th
6、en 2 76.when 星期二 then 3 77.when 星期三 then 4 78.when 星期四 then 5 79.when 星期五 then 6 80.when 星期六 then 7 81.when 星期日 then 1 82.end) 83.set dayofweek_num= 7-dayofweek_num+1 -得到本月的第一周一共有几天 84.-print dayofweek_num 85. set dd=datepart(dd,day) -得到今天是这个月的第几天 86.-print dd 87.if dd=dayofweek_num -小于前一周的天数 88.ret
7、urn 1 89.else 90.set dd=dd-dayofweek_num 91.if dd % 7=0 92. begin 93. set num=dd / 7 94. return num+1 95. 96. end 97. else -if dd % 70 98. 99. set num=dd / 7 100.set num=num+1+1 101. return num 102.end 103. 104.-常用日期的视图 105.SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS today, REPLACE(CONVERT(varcha
8、r(8), GETDATE(), 108), :, ) AS time, 106. REPLACE(REPLACE(REPLACE(CONVERT(varchar, GETDATE(), 120), ), ) AS all_date, CONVERT(varchar(12), GETDATE(), 112) AS date, 107. YEAR(GETDATE() AS year, MONTH(GETDATE() AS month, DAY(GETDATE() AS day, CONVERT(varchar(8), DATEADD(d, - 1, GETDATE(), 112) 108. AS
9、 yestaday, CONVERT(varchar(8), DATEADD(d, 1, GETDATE(), 112) AS tomorrow 109.FROM 110. 111./* 112.T-SQL: 17 个与日期时间相关的自定义函数(UDF),周日作为周的最后一天,均不受 DateFirst、语言版本影响 113.提示:114.(Datefirst + datepart(weekday,Date) % 7 判断周几是最保险的! 与 DateFirst 无关,与语言版本无关 115.DateFirst 可能会导致 datepart(weekday,Date) 不一样!116.无论 D
10、ateFirst 等于几,无论是什么语言版本的 SQL Server 下面永远恒成立!117.(Datefirst + datepart(weekday,Date)%7 : 2、3、4、5、6、0、1 分别代表 周一 到 周日 118.- */ 119. 120.create function udf_GetAge(StartDate datetime,EndDate datetime) 121.returns integer 122.- 返回精确年龄 select dbo.udf_GetAge(1949-10-01,getdate() 123.begin 124.return datedif
11、f(year,StartDate,EndDate) 125. - case when datediff(day,dateadd(year,datediff(year,StartDate,EndDate),StartDate),EndDate) = 0 126. then 0 127. else 128. 1 129. end 130.end 131. 132.go 133. 134.create function udf_DaysOfYearByDate(Date datetime) 135.returns integer 136.- 返回年的天数 可判断 平(365)、润(366) 年 13
12、7.begin 138.return datediff(day,dateadd(year,datediff(year,0,Date),0),dateadd(year,datediff(year,0,Date) + 1,0) 139.end 140. 141.go 142. 143.create function udf_DaysOfYear(Year integer) 144.returns integer 145.- 返回年的天数 可判断 平(365)、润(366) 年 146.begin 147.return datediff(day,dateadd(year,year - year(0)
13、,0),dateadd(year,year - year(0) + 1,0) 148.end 149. 150.go 151. 152.create function udf_HalfDay(Date datetime) 153.returns datetime 154.- 返回 Date 是 上午 返回 Date 的零点,Date 是 下午 返回 Date 的十二点 155.begin 156.return case when datepart(hour,Date) 12 157. then dateadd(day,datediff(day,0,Date),0) -上午归到 零点 158.
14、else 159. dateadd(hour,12,dateadd(day,datediff(day,0,Date),0) -下午归到 十二点 160. end 161.end 162. 163.go 164. 165.create function udf_WeekDiff(StartDate datetime,EndDate datetime) 166.returns integer 167.- 返回 StartDate , EndDate 之间周数 周日是当周的最后一天 168.begin 169.return datediff(week,StartDate,EndDate) - + 1
15、 170. + case when (Datefirst + datepart(weekday,StartDate) % 7 = 1 171. then 1 172. else 173. 0 174. end 175. - case when (Datefirst + datepart(weekday,EndDate) % 7 = 1 176. then 1 177. else 0 178. end 179.end 180. 181.go 182. 183.create function udf_WeekOfMonth(Date datetime) 184.- 返回 Date 是所在月的第几周
16、 周日是当周的最后一天 185.returns integer 186.begin 187.return datediff(week 188. ,case when (Datefirst + datepart(weekday,dateadd(month,datediff(month,0,Date),0) % 7 = 1 189. then dateadd(month,datediff(month,0,Date),0) - 1 190. else 191. dateadd(month,datediff(month,0,Date),0) 192. end 193. ,case when (Date
17、first + datepart(weekday,Date) % 7 = 1 194. then Date-1 195. else Date 196. ) 197.end 198.-流水号编码方式 199.-现实生活中的流水帐号的格式有五花八门,本来主要探讨一种比较普遍的格式2位字符串+4位年月日期+4位递增数字(如MA2009090001),其他格式都不是问题。200. 201.Declare SerialLong int,i int,ObjLeng int 202.set SerialLong = 4 -定义最大的单号长度位数 203.Declare NowDate varchar(10)
18、 204.set NowDate = GetDate() -取得本日完整日期 205.Declare NowYear char(4),NowMonth char(2),NowDay char(2) 206.set NowYear = year(NowDate) -取得年份 207.set NowMonth = Month(NowDate) -取得月份 208.set NowDay = Day(NowDate) -取得日期 209.Declare FullYear char(4), fullMonth char(2),FullDay char(2),FullYMD char(8) 210.set
19、 fullyear = NowYear 211.-select fullyear 212.if Len(NowMonth) = 1 -如果月份为10月以下则在月份前加0 213.Begin 214.set FullMonth = 0 + NowMonth 215.End 216.Else 217.Begin 218.set FullMonth = NowMonth 219.end 220.-select FullMonth 221.-IF LEN(NOWDAY) = 1 -如果日期小于10则在前面加0 222.-BEGIN 223.-SET FULLDAY = + NOWDAY 224.-EN
20、D 225.-Else 226.-Begin 227.-set FullDay = NowDay 228.-End 229.-组合今日的完整日期 230.-set FullYmd = MA+fullyear+fullMonth+FullDay 231.set FullYmd = +fullyear+fullMonth 232.-select FullYmd 233.Declare MaxSerial char(4) 234.-取得最大的序列号,如果为空时默认编为0:此处请改为自已对应表的对应字段进行查询 235.select MaxSerial = isNull(SUBSTRING(MAX(U
21、serID),9,4),) From A_CY_AdminUser 236. 237.IF isnumeric(MaxSerial) = 1 -检查当前的流水号是否为数值 238. Begin 239. set MaxSerial = MaxSerial + 1 240. set ObjLeng = Len(MaxSerial) 241. IF ObjLeng SerialLong 242. begin 243. set i = 0 244. While i (SerialLong-ObjLeng) -循环判断是否小于定义长度,小于则在前面+0 245. Begin 246. set MaxS
22、erial = + convert(varchar(4),MaxSerial) 247. set i = i + 1 248. IF i dt_end 287. RETURN(DATEDIFF(Day,dt_begin,dt_end) 288. +1-( 289. SELECT COUNT(*) FROM tb_Holiday 290. WHERE HDate BETWEEN dt_begin AND dt_end) 291. RETURN(-(DATEDIFF(Day,dt_end,dt_begin) 292. +1-( 293. SELECT COUNT(*) FROM tb_Holiday 294. WHERE HDate BETWEEN dt_end AND dt_begin) 295.END 296.GO 297.if exists (select * from dbo.sysobjects where id = object_id(Ndbo.f_WorkDayADD298.drop function dbo.f_WorkDayADD 299.GO 300.-在指定日期上增加工作天数 301
copyright@ 2008-2022 冰豆网网站版权所有
经营许可证编号:鄂ICP备2022015515号-1