sql 显示星期几函数

青旅半醒 2021-12-13 00:45 633阅读 0赞

--返回第幾周
    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