SQL命令.doc

SQL -本周第一天 SELECT DATEADD(Day,1-(DATEPART(Weekday,getdate()+DATEFIRST-1)%7,getdate() -or select dateadd(wk, datediff(wk,0,getdate(), 0) -本周第一天 select dateadd(wk, datediff(wk,0,getdate(), 6) -上月第一天 SELECT CONVERT(CHAR(10),DATEADD(month,-1,DATEADD(dd,-DAY(GETDATE()+1,GETDATE(),111) -上月最后一天 SELECT CONVERT(CHAR(10),DATEADD(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate(),0),111)+ 23:59:59 -本月第一天 select dateadd(dd,-datepart(dd,getdate()+1,getdate() -本月最后一天 select dateadd(dd,-datepart(dd,getdate() ,dateadd(mm,1,getdate() -本月天数 select datediff(dd,dateadd(dd,-datepart(dd,getdate()+1,getdate(), dateadd(dd,-datepart(dd,getdate()+1,dateadd(mm,1,getdate() -or select datepart(dd,dateadd(dd,-1,dateadd(mm,1,cast(cast(year(getdate() as varchar)+-+cast(month(getdate() as varchar)+-01 as datetime) -下月第一天 select dateadd(dd,-datepart(dd,getdate()+1,dateadd(mm,1,getdate() -下月最后一天 SELECT CONVERT(CHAR(10),DATEADD(ms,-3,DATEADD(mm,DATEDIFF(m,0,getdate()+2,0),111)+ 23:59:59 -季度第一天 SELECT DATEADD(qq, DATEDIFF(qq,0,getdate(), 0) -季度最后一天（直接推算法） SELECT DATEADD(Day,-1,CONVERT(char(8),DATEADD(Month,1+DATEPART(Quarter,getdate()*3-Month(getdate(),getdate(),120)+1) -季度的最后一天（CASE判断法） select DATEADD(Month,DATEPART(Quarter,getdate()*3-Month(getdate(),getdate() -本月第一个星期一 SELECT DATEADD(wk, DATEDIFF(wk, , DATEADD(dd, 6 - DAY(getdate(), getdate(), ) -去年最后一天 SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate(), 0) -今年第一天 SELECT DATEADD(yy, DATEDIFF(yy,0,getdate(), 0) -今年最后一天 SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()+1,0) -指定日期所在周的任意一天 SELECT DATEADD(Day,number-DATEPART(Weekday,dt),dt)-5指定日期所在周的任意星期几 -A. 星期天做为一周的第1天 SELECT DATEADD(Day,number-(DATEPART(Weekday,dt)+DATEFIRST-1)%7,dt) -B. 星期一做为一周的第1天 SELECT DATEADD(Day,number-(DATEPART(Weekday,dt)+DATEFIRST-2)%7-1,dt) -周内的第几日 select datepart(weekday,getdate() as 周内的第几日 -年内的第几周 select datepart(week,getdate() as 年内的第几周 -年内的第几季 select datepart(quarter,getdate() as 年内的第几季 -判断某天是当月的第几周的sql函数 CREATE FUNCTION WeekOfMonth(day datetime) RETURNS int AS begin -declare day datetime declare num int declare Start datetime declare dd int declare dayofweek char(8) declare dayofweek_num char(8) declare startWeekDays int -set day=2009-07-05 if datepart(dd,day)=1 return 1 else set Start= (SELECT DATEADD(mm, DATEDIFF(mm,0,day), 0) -一个月第一天的 set dayofweek= (datename(weekday,Start) -得到本月第一天是周几 set dayofweek_num=(select (case dayofweek when 星期一 then 2 when 星期二 then 3 when 星期三 then 4 when 星期四 then 5 when 星期五 then 6 when 星期六 then 7 when 星期日 then 1 end) set dayofweek_num= 7-dayofweek_num+1 -得到本月的第一周一共有几天 -print dayofweek_num set dd=datepart(dd,day) -得到今天是这个月的第几天 -print dd if dd=dayofweek_num -小于前一周的天数 return 1 else set dd=dd-dayofweek_num if dd % 7=0 begin set num=dd / 7 return num+1 end else -if dd % 70 set num=dd / 7 set num=num+1+1 return num end -常用日期的视图 SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS today, REPLACE(CONVERT(varchar(8), GETDATE(), 108), :, ) AS time, REPLACE(REPLACE(REPLACE(CONVERT(varchar, GETDATE(), 120), -, ), , ), :, ) AS all_date, CONVERT(varchar(12), GETDATE(), 112) AS date, YEAR(GETDATE() AS year, MONTH(GETDATE() AS month, DAY(GETDATE() AS day, CONVERT(varchar(8), DATEADD(d, - 1, GETDATE(), 112) AS yestaday, CONVERT(varchar(8), DATEADD(d, 1, GETDATE(), 112) AS tomorrow FROM /* T-SQL: 17 个与日期时间相关的自定义函数(UDF),周日作为周的最后一天,均不受 DateFirst、语言版本影响 提示: (Datefirst + datepart(weekday,Date) % 7 判断周几是最保险的! 与 DateFirst 无关,与语言版本无关 DateFirst 可能会导致 datepart(weekday,Date) 不一样! 无论 DateFirst 等于几,无论是什么语言版本的 SQL Server 下面永远恒成立! (Datefirst + datepart(weekday,Date)%7 : 2、3、4、5、6、0、1 分别代表 周一 到 周日 - */ create function udf_GetAge(StartDate datetime,EndDate datetime) returns integer - 返回精确年龄 select dbo.udf_GetAge(1949-10-01,getdate() begin return datediff(year,StartDate,EndDate) - case when datediff(day,dateadd(year,datediff(year,StartDate,EndDate),StartDate),EndDate) = 0 then 0 else 1 end end go create function udf_DaysOfYearByDate(Date datetime) returns integer - 返回年的天数 可判断 平(365)、润(366) 年 begin return datediff(day,dateadd(year,datediff(year,0,Date),0),dateadd(year,datediff(year,0,Date) + 1,0) end go create function udf_DaysOfYear(Year integer) returns integer - 返回年的天数 可判断 平(365)、润(366) 年 begin return datediff(day,dateadd(year,year - year(0),0),dateadd(year,year - year(0) + 1,0) end go create function udf_HalfDay(Date datetime) returns datetime - 返回 Date 是 上午 返回 Date 的零点,Date 是 下午 返回 Date 的十二点 begin return case when datepart(hour,Date) 12 then dateadd(day,datediff(day,0,Date),0) -上午归到 零点 else dateadd(hour,12,dateadd(day,datediff(day,0,Date),0) -下午归到 十二点 end end go create function udf_WeekDiff(StartDate datetime,EndDate datetime) returns integer - 返回 StartDate , EndDate 之间周数 周日是当周的最后一天 begin return datediff(week,StartDate,EndDate) - + 1 + case when (Datefirst + datepart(weekday,StartDate) % 7 = 1 then 1 else 0 end - case when (Datefirst + datepart(weekday,EndDate) % 7 = 1 then 1 else 0 end end go create function udf_WeekOfMonth(Date datetime) - 返回 Date 是所在月的第几周 周日是当周的最后一天 returns integer begin return datediff(week ,case when (Datefirst + datepart(weekday,dateadd(month,datediff(month,0,Date),0) % 7 = 1 then dateadd(month,datediff(month,0,Date),0) - 1 else dateadd(month,datediff(month,0,Date),0) end ,case when (Datefirst + datepart(weekday,Date) % 7 = 1 then Date-1 else Date ) end -流水号编码方式 -现实生活中的流水帐号的格式有五花八门，本来主要探讨一种比较普遍的格式2位字符串+4位年月日期+4位递增数字（如MA2009090001），其他格式都不是问题。 Declare SerialLong int,i int,ObjLeng int set SerialLong = 4 -定义最大的单号长度位数 Declare NowDate varchar(10) set NowDate = GetDate() -取得本日完整日期 Declare NowYear char(4),NowMonth char(2),NowDay char(2) set NowYear = year(NowDate) -取得年份 set NowMonth = Month(NowDate) -取得月份 set NowDay = Day(NowDate) -取得日期 Declare FullYear char(4), fullMonth char(2),FullDay char(2),FullYMD char(8) set fullyear = NowYear -select fullyear if Len(NowMonth) = 1 -如果月份为10月以下则在月份前加0 Begin set FullMonth = 0 + NowMonth End Else Begin set FullMonth = NowMonth end -select FullMonth -IF LEN(NOWDAY) = 1 -如果日期小于10则在前面加0 -BEGIN -SET FULLDAY = 0 + NOWDAY -END -Else -Begin -set FullDay = NowDay -End -组合今日的完整日期 -set FullYmd = MA+fullyear+fullMonth+FullDay set FullYmd = MA+fullyear+fullMonth -select FullYmd Declare MaxSerial char(4) -取得最大的序列号,如果为空时默认编为0:此处请改为自已对应表的对应字段进行查询 select MaxSerial = isNull(SUBSTRING(MAX(UserID),9,4),0) From A_CY_AdminUser IF isnumeric(MaxSerial) = 1 -检查当前的流水号是否为数值 Begin set MaxSerial = MaxSerial + 1 set ObjLeng = Len(MaxSerial) IF ObjLeng SerialLong begin set i = 0 While i (SerialLong-ObjLeng) -循环判断是否小于定义长度,小于则在前面+0 Begin set MaxSerial = 0 + convert(varchar(4),MaxSerial) set i = i + 1 IF i dt_end RETURN(DATEDIFF(Day,dt_begin,d