--本周第一天
SELECTDATEADD(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)
--上月第一天
SELECTCONVERT(CHAR(10),DATEADD(month,-1,DATEADD(dd,-DAY(GETDATE())+1,GETDATE())),111)
--上月最后一天
SELECTCONVERT(CHAR(10),DATEADD(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()),0)),111)+'23:59:59'
--本月第一天
selectdateadd(dd,-datepart(dd,getdate())+1,getdate())
--本月最后一天
selectdateadd(dd,-datepart(dd,getdate()) ,dateadd(mm,1,getdate()))
--本月天数
selectdatediff(dd,dateadd(dd,-datepart(dd,getdate())+1,getdate()),dateadd(dd,-datepart(dd,getdate())+1,dateadd(mm,1,getdate())))
--or
selectdatepart(dd,dateadd(dd,-1,dateadd(mm,1,cast(cast(year(getdate()) asvarchar)+'-'+cast(month(getdate()) as varchar)+'-01' as datetime))))
--下月第一天
selectdateadd(dd,-datepart(dd,getdate())+1,dateadd(mm,1,getdate()))
--下月最后一天
SELECTCONVERT(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)
--季度最后一天(直接推算法)
SELECTDATEADD(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))
--指定日期所在周的任意一天
SELECTDATEADD(Day,@number-DATEPART(Weekday,@dt),@dt)--5.指定日期所在周的任意星期几
--A. 星期天做为一周的第1天
SELECTDATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-1)%7,@dt)
--B. 星期一做为一周的第1天
SELECTDATEADD(Day,@number-(DATEPART(Weekday,@dt)+@@DATEFIRST-2)%7-1,@dt)
---周内的第几日
selectdatepart(weekday,getdate()) as 周内的第几日
--年内的第几周
selectdatepart(week,getdate()) as 年内的第几周
--年内的第几季
selectdatepart(quarter,getdate()) as 年内的第几季
--判断某天是当月的第几周的sql函数
CREATE FUNCTION WeekOfMonth(@day datetime)
RETURNS int
AS
begin
----declare @daydatetime
declare @numint
declare @Startdatetime
declare @ddint
declare @dayofweekchar(8)
declare@dayofweek_num char(8)
declare@startWeekDays int
---set@day='2009-07-05'
ifdatepart(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) AStoday, 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 functionudf_GetAge(@StartDate datetime,@EndDate datetime)
returnsinteger
-- 返回精确年龄 select dbo.udf_GetAge('1949-10-01',getdate())
begin
returndatediff(year,@StartDate,@EndDate)
- case whendatediff(day,dateadd(year,datediff(year,@StartDate,@EndDate),@StartDate),@EndDate)>= 0
then 0
else
1
end
end
go
create functionudf_DaysOfYearByDate(@Date datetime)
returnsinteger
-- 返回年的天数可判断平(365)、润(366) 年
begin
returndatediff(day,dateadd(year,datediff(year,0,@Date),0),dateadd(year,datediff(year,0,@Date)+ 1,0))
end
go
create functionudf_DaysOfYear(@Year integer)
returnsinteger
-- 返回年的天数可判断平(365)、润(366) 年
begin
returndatediff(day,dateadd(year,@year - year(0),0),dateadd(year,@year - year(0) +1,0))
end
go
create functionudf_HalfDay(@Date datetime)
returnsdatetime
-- 返回@Date 是上午返回 @Date 的零点,@Date 是下午返回 @Date 的十二点
begin
return case whendatepart(hour,@Date) < 12
thendateadd(day,datediff(day,0,@Date),0) --上午归到零点
else
dateadd(hour,12,dateadd(day,datediff(day,0,@Date),0)) --下午归到十二点
end
end
go
create functionudf_WeekDiff(@StartDate datetime,@EndDate datetime)
returnsinteger
-- 返回[@StartDate , @EndDate] 之间周数周日是当周的最后一天
begin
returndatediff(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 functionudf_WeekOfMonth(@Date datetime)
-- 返回@Date 是所在月的第几周周日是当周的最后一天
returnsinteger
begin
returndatediff(week
,case when (@@Datefirst +datepart(weekday,dateadd(month,datediff(month,0,@Date),0))) % 7 = 1
thendateadd(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 @NowDatevarchar(10)
set @NowDate =GetDate() --取得本日完整日期
Declare @NowYearchar(4),@NowMonth char(2),@NowDay char(2)
set @NowYear =year(@NowDate) --取得年份
set @NowMonth =Month(@NowDate) --取得月份
set @NowDay =Day(@NowDate) --取得日期
Declare @FullYearchar(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 @MaxSerialchar(4)
--取得最大的序列号,如果为空时默认编为0:此处请改为自已对应表的对应字段进行查询
select @MaxSerial= isNull(SUBSTRING(MAX(UserID),9,4),'0') From A_CY_AdminUser
IFisnumeric(@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]') andOBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table[tb_Holiday]
GO
--定义节假日表
CREATE TABLEtb_Holiday(
HDatesmalldatetime 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 FUNCTIONf_WorkDay(
@dt_begindatetime, --计算的开始日期
@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 xtypein (N'FN', N'IF', N'TF'))
drop function[dbo].[f_WorkDayADD]
GO
--在指定日期上增加工作天数
CREATE FUNCTIONf_WorkDayADD(
@date datetime, --基础日期
@workday int --要增加的工作日数
)RETURNSdatetime
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)
来源:百度文库