Home > SQL Server > SQL – Lấy ngày đầu tiên trong tuần

SQL – Lấy ngày đầu tiên trong tuần

Tạo function : 

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
 
CREATE FUNCTION dbo.F_START_OF_WEEK
(
 @DATE datetime,
 -- Sun = 1, Mon = 2, Tue = 3, Wed = 4
 -- Thu = 5, Fri = 6, Sat = 7
 -- Default to Sunday
 @WEEK_START_DAY int = 1 
)
/*
Find the fisrt date on or before @DATE that matches 
day of week of @WEEK_START_DAY.
*/
returns datetime
as
begin
declare @START_OF_WEEK_DATE datetime
declare @FIRST_BOW datetime
 
-- Check for valid day of week
if @WEEK_START_DAY between 1 and 7
 begin
 -- Find first day on or after 1753/1/1 (-53690)
 -- matching day of week of @WEEK_START_DAY
 -- 1753/1/1 is earliest possible SQL Server date.
 select @FIRST_BOW = convert(datetime,-53690+((@WEEK_START_DAY+5)%7))
 -- Verify beginning of week not before 1753/1/1
 if @DATE >= @FIRST_BOW
 begin
 select @START_OF_WEEK_DATE = 
 dateadd(dd,(datediff(dd,@FIRST_BOW,@DATE)/7)*7,@FIRST_BOW)
 end
 end
 
return @START_OF_WEEK_DATE
 
end
 
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Sử dụng : 

SELECT dbo.F_START_OF_WEEK(GETDATE(),1)

Tùy chỉnh : 

Tham số @WEEK_START_DAY cho phép bạn chọn lựa ngày bắt đầu trong tuần là thứ mấy .

1. Chủ Nhật
2. Thứ 2
3. Thứ 3
4. Thứ 4
5. Thứ 5
6. Thứ 6
7. Thứ 7

(Link tham khảo : http://dev.meotom.net/2009/10/27/Lay-ngay-dau-tien-trong-tuan-dung-SQL-Server-22A )

 

Advertisements
Categories: SQL Server
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: