获取SQL 执行时间、日期与长整型换转代码,MSSQL代码库,德仔网

代码语言
代码分类
【MSSQL】获取SQL 执行时间、日期与长整型换转
作者:Dezai.CN / 发布于2013/3/26/ 1419

	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
试试其它关键字
　SQL　　执行时间　
同语言下
可能有用的
Dezai.CN贡献的其它代码(4037)