sql 显示星期几函数
01 --返回第幾周
02 DECLARE @Dt datetime
03
04 SELECT @Dt='2008-02-21'
05
06 SELECT DATEPART( wk, @Dt)
07
08 SELECT DATEPART( wk, GETDATE())
09 SELECT DATEPART(weekday, GETDATE())
10
11 SELECT * FROM WorkAttendanceReport
12 SELECT [dbo].[GetWeekName] (WorkAttendanceDatetime) FROM WorkAttendanceReport
13
14
15 ---WorkAttendanceDatetime
16 declare @Date smalldatetime,@weekint int,@weekname nvarchar(50)
17 set @Date='2011-08-15'
18 select @weekint= DATEPART(weekday, @Date)
19 --SELECT @weekint ---塗聚文 Geovin Du
20 --select @weekint=DATEPART(weekday, WorkAttendanceDatetime) from WorkAttendanceReport WHERE WorkAttendanceID=1
21 SELECT @weekint
22
23 if @weekint=1
24 SET @weekname=N'星期日'
25 if @weekint=2
26 SET @weekname=N'星期一'
27 if @weekint=3
28 SET @weekname=N'星期二'
29 if @weekint=4
30 SET @weekname=N'星期三'
31 if @weekint=5
32 SET @weekname=N'星期四'
33 if @weekint=6
34 SET @weekname=N'星期五'
35 if @weekint=7
36 SET @weekname=N'星期六'
37 SELECT @weekname
38
39 ---返回星期幾
40 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetWeekName]') and xtype in (N'FN', N'IF', N'TF'))
41 drop function [dbo].[GetWeekName]
42 GO
43 CREATE function GetWeekName
44 (
45 @Date smalldatetime --参数
46 )
47 returns nvarchar(500)
48 as
49 begin
50 declare @weekint int,@weekname nvarchar(50)
51 --set @Date='2011-08-15'
52 select @weekint= DATEPART(weekday, @Date)
53 --SELECT @weekint
54 --select @weekint=DATEPART(weekday, WorkAttendanceDatetime) from WorkAttendanceReport WHERE WorkAttendanceID=1
55 --SELECT @weekint
56
57 if @weekint=1
58 SET @weekname=N'星期日'
59 if @weekint=2
60 SET @weekname=N'星期一'
61 if @weekint=3
62 SET @weekname=N'星期二'
63 if @weekint=4
64 SET @weekname=N'星期三'
65 if @weekint=5
66 SET @weekname=N'星期四'
67 if @weekint=6
68 SET @weekname=N'星期五'
69 if @weekint=7
70 SET @weekname=N'星期六'
71 RETURN @weekname
72 end
73 GO
view sourceprint?01 CREATE FUNCTION [dbo].[firstOfWeek]
02 (@DATE DATETIME
03 ,@WEEK_START_DAY INT = 1)
04
05 RETURNS DATETIME
06
07 /* @date = date to calculate
08 @WEEK_START_DAY = the dayNumber of the first day of the week
09
10 Sun = 1, Mon = 2, Tue = 3, Wed = 4
11 Thu = 5, Fri = 6, Sat = 7
12 Default to Sunday
13 */
14 AS
15 BEGIN
16
17 DECLARE @START_OF_WEEK_DATE DATETIME
18
19 -- Check for valid day of week
20 IF @WEEK_START_DAY between 1 and 7
21 BEGIN
22 SET
23 @START_OF_WEEK_DATE =
24 CASE WHEN @WEEK_START_DAY - datepart(dw,@DATE) > 0 THEN
25 dateadd(day, @WEEK_START_DAY - datepart(dw,@DATE) - 7, @DATE)
26 ELSE
27 dateadd(day, @WEEK_START_DAY - datepart(dw,@DATE), @DATE)
28 END
29 END
30
31 RETURN @START_OF_WEEK_DATE
32
33 END
