#mssql
CREATE FUNCTION [dbo].[fn_GetWeekDay]
(
@InputDate Date,
-- @FirstDayOfWeek取值为1到7,若为1,表明周一是一周的第一天,若为7,表明周日是一周的第一天。
@FirstDayOfWeek int = 1
)
RETURNS int
AS
BEGIN
RETURN DATEPART(DW,DATEADD(d,@@DATEFIRST-@FirstDayOfWeek,@InputDate))
END
CREATE FUNCTION [dbo].[fn_GetWeekNumber]
(
@InputDate Date,
-- @FirstDayOfWeek取值为1到7,若为1,表明周一是一周的第一天,若为7,表明周日是一周的第一天。
@FirstDayOfWeek int = 1
)
RETURNS int
AS
BEGIN
DECLARE @WeekDayOfNewYearDay int
SET @WeekDayOfNewYearDay = [dbo].fn_GetWeekDay(DATENAME(Year,@InputDate)+'-01-01',@FirstDayOfWeek)
RETURN CEILING((DATEPART(DY,@InputDate)-(@FirstDayOfWeek-@WeekDayOfNewYearDay+1))/7)+1
END
#mysql
#1为周一为第一天
select YEARWEEK(DATE_SUB( curdate(),INTERVAL 1 day ) ,1)
#0为周日为第一天
select YEARWEEK(DATE_SUB( curdate(),INTERVAL 1 day ) )