sql 显示星期几函数

--返回第幾周
DECLARE @Dt datetime

SELECT @Dt='2008-02-21'

SELECT DATEPART( wk, @Dt)

SELECT DATEPART( wk, GETDATE())
SELECT DATEPART(weekday, GETDATE())

SELECT * FROM WorkAttendanceReport
SELECT  [dbo].[GetWeekName] (WorkAttendanceDatetime) FROM WorkAttendanceReport


---WorkAttendanceDatetime
declare @Date smalldatetime,@weekint int,@weekname nvarchar(50)
set @Date='2011-08-15' 
select @weekint= DATEPART(weekday, @Date)
--SELECT @weekint   ---塗聚文 Geovin Du
--select @weekint=DATEPART(weekday, WorkAttendanceDatetime) from WorkAttendanceReport WHERE WorkAttendanceID=1
SELECT @weekint

if @weekint=1
  	SET @weekname=N'星期日'
if @weekint=2
	SET @weekname=N'星期一'
if @weekint=3
	SET @weekname=N'星期二'
if @weekint=4
	SET @weekname=N'星期三'
if @weekint=5
	SET @weekname=N'星期四'
if @weekint=6
	SET @weekname=N'星期五'
if @weekint=7
	SET @weekname=N'星期六'
SELECT @weekname

---返回星期幾
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetWeekName]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[GetWeekName]
GO
CREATE   function  GetWeekName
(  
   @Date smalldatetime --参数
)
returns nvarchar(500)
as
begin
declare @weekint int,@weekname nvarchar(50)
--set @Date='2011-08-15' 
select @weekint= DATEPART(weekday, @Date)
--SELECT @weekint
--select @weekint=DATEPART(weekday, WorkAttendanceDatetime) from WorkAttendanceReport WHERE WorkAttendanceID=1
--SELECT @weekint

if @weekint=1
  	SET @weekname=N'星期日'
if @weekint=2
	SET @weekname=N'星期一'
if @weekint=3
	SET @weekname=N'星期二'
if @weekint=4
	SET @weekname=N'星期三'
if @weekint=5
	SET @weekname=N'星期四'
if @weekint=6
	SET @weekname=N'星期五'
if @weekint=7
	SET @weekname=N'星期六'
RETURN @weekname
end
GO

CREATE FUNCTION [dbo].[firstOfWeek]
    (@DATE DATETIME 
    ,@WEEK_START_DAY INT = 1)

RETURNS DATETIME 

    /*  @date = date to calculate
        @WEEK_START_DAY = the dayNumber of the first day of the week

	    Sun = 1, Mon = 2, Tue = 3, Wed = 4
	    Thu = 5, Fri = 6, Sat = 7
	    Default to Sunday
    */
AS
BEGIN
    	
    DECLARE @START_OF_WEEK_DATE DATETIME

    -- Check for valid day of week
    IF @WEEK_START_DAY between 1 and 7
        BEGIN	
            SET 
                @START_OF_WEEK_DATE = 
                    CASE WHEN @WEEK_START_DAY - datepart(dw,@DATE) > 0 THEN
                        dateadd(day, @WEEK_START_DAY - datepart(dw,@DATE) - 7, @DATE)
                    ELSE
                        dateadd(day, @WEEK_START_DAY - datepart(dw,@DATE), @DATE)
                    END         
		END

    RETURN @START_OF_WEEK_DATE

END

转载于:https://www.cnblogs.com/geovindu/archive/2011/08/15/2139751.html