sql语句学习

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 % 7<>0   
     
     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 < (@SerialLong-@ObjLeng)  
            continue  
            Else  
            break  
            End  
            End  
      End  
IF len(@MaxSerial) = @SerialLong     --判断序列号是否已满9999   
   Begin  
   Declare @FullSerial char(12)  
   set @FullSerial = @FullYMD + @MaxSerial   --组合新的完整编号   
   End  
else  
   Begin  
   set @FullSerial = ''  
   End  
--Select @FullSerial   
  
  
--假日表   
if exists (select * from dbo.sysobjects where id = object_id(N'[tb_Holiday]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)  
drop table [tb_Holiday]  
GO  
--定义节假日表   
CREATE TABLE tb_Holiday(  
HDate smalldatetime primary key clustered, --节假日期   
Name nvarchar(50) not null)             --假日名称   
GO  
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_WorkDay]') and xtype in (N'FN', N'IF', N'TF'))  
drop function [dbo].[f_WorkDay]  
GO  
--计算两个日期之间的工作天数   
CREATE FUNCTION f_WorkDay(  
@dt_begin datetime,  --计算的开始日期   
@dt_end  datetime   --计算的结束日期   
)RETURNS int  
AS  
BEGIN  
 IF @dt_begin>@dt_end  
  RETURN(DATEDIFF(Day,@dt_begin,@dt_end)  
   +1-(  
    SELECT COUNT(*) FROM tb_Holiday  
    WHERE HDate BETWEEN @dt_begin AND @dt_end))  
 RETURN(-(DATEDIFF(Day,@dt_end,@dt_begin)  
  +1-(  
   SELECT COUNT(*) FROM tb_Holiday  
   WHERE HDate BETWEEN @dt_end AND @dt_begin)))  
END  
GO  
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[f_WorkDayADD]') and xtype in (N'FN', N'IF', N'TF'))  
drop function [dbo].[f_WorkDayADD]  
GO  
--在指定日期上增加工作天数   
CREATE FUNCTION f_WorkDayADD(  
@date    datetime,  --基础日期   
@workday int       --要增加的工作日数   
)RETURNS datetime  
AS  
BEGIN  
 IF @workday>0  
  WHILE @workday>0  
   SELECT @date=@date+@workday,@workday=count(*)  
   FROM tb_Holiday  
   WHERE HDate BETWEEN @date AND @date+@workday  
 ELSE  
  WHILE @workday<0  
   SELECT @date=@date+@workday,@workday=-count(*)  
   FROM tb_Holiday  
   WHERE HDate BETWEEN @date AND @date+@workday  
 RETURN(@date)  
END