/*
declare @datetime datetime
set @datetime = '2018-08-27 10:20:25.001'
select dbo.fn_trunc(@datetime, 'yyyy') as [本年第一天],
dbo.fn_trunc(@datetime, 'qq') as [本季第一天quarter],
dbo.fn_trunc(@datetime, 'mm') as [本月第一天month],
dbo.fn_trunc(@datetime, 'dd') as [當天now],
dbo.fn_trunc(@datetime, 'day') as [本周第一天weekday]
本年第一天 本季第一天quarter 本月第一天month 當天now 本周第一天weekday
----------------------- ----------------------- ----------------------- ----------------------- -----------------------
2018-01-01 00:00:00.000 2018-07-01 00:00:00.000 2018-08-01 00:00:00.000 2018-08-27 00:00:00.000 2018-08-26 10:20:25.000
*/
alter function dbo.fn_trunc(
@datetime datetime,
@datepart varchar(30)
)
returns datetime
as
begin
/*
@datepart：
yyyy: 本年度第一天
qq: 本季度第一天
q: 本季度第一天
mm: 本月第一天
dd: 當天
day: 本周第一天
*/
declare @result datetime,
@yyyy varchar(4),
@qq int,
@mm int,
@dd varchar(4),
@day varchar(4),
@yyyymm varchar(10),
@yyyymmdd varchar(10);
if @datetime = '1900-01-01'
set @datetime = null;
if @datetime is null
set @result = null;
if (@datetime is not null) and (@datepart= 'yyyy')
set @result = cast(datepart(yyyy, @datetime) as varchar(10)) + '-01-01'
if (@datetime is not null) and (@datepart='qq')
begin
set @qq = datepart(qq,@datetime)
if @qq = 1
set @result = cast(datepart(yyyy, @datetime) as varchar(10)) + '-01-01' ;
if @qq =2
set @result = cast(datepart(yyyy, @datetime) as varchar(10)) + '-04-01' ;
if @qq =3
set @result = cast(datepart(yyyy, @datetime) as varchar(10)) + '-07-01' ;
if @qq =4
set @result = cast(datepart(yyyy, @datetime) as varchar(10)) + '-10-01' ;
end;
if (@datetime is not null) and (@datepart= 'mm')
begin
set @yyyymm = convert(varchar(7), @datetime, 120 ) -- 'yyyy-mm'
set @result = @yyyymm + '-01'
end;
if (@datetime is not null) and (@datepart= 'dd')
begin
set @yyyymmdd = convert(varchar(10), @datetime, 120 ) -- 'yyyy-mm-dd'
set @result = @yyyymmdd
end;
if (@datetime is not null) and (@datepart= 'day')
begin
set @result = DATEADD(Day,0-(DATEPART(Weekday,@datetime)+@@DATEFIRST-1)%7,@datetime)
end;
if @result is not null
set @result = cast( @result as datetime);
else
set @result = null;
return(@result)
end
go