获取SQL 执行时间、日期与长整型换转

拼搏现实的明天。 2022-07-03 14:56 144阅读 0赞

create PROCEDURE pr_get_int64_system_time
    AS
    set nocount on
     BEGIN
      SELECT system_time = CONVERT(BIGINT,DATEDIFF(S,'1970-01-01 08:00:00.000',GETDATE()))*1000+DATEPART(MS,GETDATE());
     END
    set nocount off
    GO
    create PROCEDURE pr_gk_display_64time
     @millisecond BIGINT
    AS
    set nocount on
     DECLARE @second BIGINT , @now DATETIME
     BEGIN
      SET @second = @millisecond / 1000
      SET @millisecond = @millisecond % 1000
      SET @now = DATEADD(S, @second,'1970-01-01 08:00:00.000'); 
      SET @now = DATEADD(Ms, @millisecond, @now); 
      print CONVERT(VARCHAR(50),@now,21);
     END
    set nocount off
    GO
    Create PROCEDURE pr_get_run_time
     @SQL NVARCHAR(MAX)
    as
    set nocount on
     BEGIN
      declare @now_date datetime
      declare @run_ms bigint
       set @now_date=GETDATE();
       EXEC SP_EXECUTESQL @SQL;
       SELECT @run_ms=DATEDIFF(MS,@now_date,GETDATE())
       print @run_ms
     END 
    set nocount off 
    
     
    
    ---每个月的起止日期
    
    declare @startdate datetime,@enddate datetime
    set @startdate=dateadd(mm,datediff(mm,0,getdate()),0)
    set @enddate=dateadd(ms,-2,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))
    select convert(varchar(10),dateadd(day,number,@startdate),120) as col
    from master..spt_values 
    where datediff(day,dateadd(day,number,@startdate), @enddate)>=0 and number>=0 and type='p'
    
     --一年内的所有周的起止日期
    
    set datefirst 7--设置每周的起始日期为 周末（美国（数据库）默认）
    ;with ach as
    (
        select dateadd(dd,number,cast(ltrim(year(getdate()))+'-01-01' as datetime)) date
        from master..spt_values
        where [type] = 'p' and number between 0 and 400
    ),cte as
    (
        select *
        from ach
        where year(date) = year(getdate())
    )
    
    select top 10 datepart(wk,date) as wk,min(date) mindate,max(date) maxdate
    from cte
    group by datepart(wk,date)
    
     
    
    --
    
    --本周第一天   
    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