本文介绍了如何获取星期六的日期(或任何其他工作日的日期) - SQL Server的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

如何获得星期六的日期。我有今天的日期。

  GETDATE()

如何做到这一点。



今天是 08-08-2011



我希望输出为 08-13-2011

解决方案

这是一个函数,将返回下一个星期六,如果你这样调用: / p>

  SELECT dbo.fn_Get_NextWeekDay('2011-08-08',6)
/ pre>

6来自可以为 DATEFIRST 设置的可能值列表。



您可以获得



这是函数:

  IF OBJECT_ID('dbo.fn_Get_NextWeekDay')IS NOT NULL 
DROP FUNCTION dbo.fn_Get_NextWeekDay
GO
CREATE FUNCTION dbo.fn_Get_NextWeekDay(
@aDate DATETIME
,@dayofweek INT
/ *
@dw - 星期几
1 - 星期一
2 - 星期二
3 - 星期三
4 - 周四
5 - 周五
6 - 周六
7 - 周日
* /

退货日期
AS
/ *
SELECT dbo.fn_Get_NextWeekDay('2011-08-08',6)
SELECT dbo.fn_Get_NextWeekDay('2011-08-08',1)
* /
BEGIN
返回
DATEADD(day
,(@dayofweek + 8 - DATEPART(dw,@aDate) - @@ DATEFIRST)%7
,@aDate

END
GO

/ strong>
这可能是另一种解决方案。这应该使用任何语言:

  IF OBJECT_ID('dbo.fn_NextWeekDay')IS NOT NULL 
DROP FUNCTION dbo .fn_NextWeekDay
GO
CREATE FUNCTION dbo.fn_NextWeekDay(
@aDate DATE
,@dayofweek NVARCHAR(30)

返回日期
AS
/ *
SELECT dbo.fn_NextWeekDay('2016-12-14','fri')
SELECT dbo.fn_NextWeekDay('07-3-15','mon')
* /
BEGIN
DECLARE @dx INT = 6
WHILE UPPER(DATENAME(工作日,@ aDate))不喜欢UPPER(@dayofweek)+'%'
BEGIN

SET @aDate = DATEADD(day,1,@ aDate)

SET @ dx = @ dx-1
如果@dx< 0
BEGIN
SET @aDate = NULL
BREAK
END
END

RETURN @aDate

END
GO


How to get Saturday's Date. I have today's date with me.

GETDATE()

How to do this.

For eg. TODAY is 08-08-2011

I want output as 08-13-2011

解决方案

This is a function that will return the next Saturday if you call it like this:

SELECT dbo.fn_Get_NextWeekDay('2011-08-08', 6)

The "6" comes from the list of possible values you can set for DATEFIRST.

You can get any other day of the week by changing the second parameter accordingly.

This is the function:

IF OBJECT_ID('dbo.fn_Get_NextWeekDay') IS NOT NULL 
  DROP FUNCTION dbo.fn_Get_NextWeekDay
GO
CREATE FUNCTION dbo.fn_Get_NextWeekDay(
     @aDate   DATETIME
   , @dayofweek      INT
    /*
      @dw - day of the week
      1 - Monday
      2 - Tuesday
      3 - Wednesday
      4 - Thursday
      5 - Friday
      6 - Saturday
      7 - Sunday
    */   
  )
RETURNS DATETIME 
AS
/*
  SELECT dbo.fn_Get_NextWeekDay('2011-08-08', 6)
  SELECT dbo.fn_Get_NextWeekDay('2011-08-08', 1)
*/
BEGIN
  RETURN 
      DATEADD(day
        , ( @dayofweek + 8 - DATEPART(dw, @aDate) - @@DATEFIRST ) % 7
        , @aDate 
      )  
END
GO

[EDIT]This might be another solution. This should work in any language:

IF OBJECT_ID('dbo.fn_NextWeekDay') IS NOT NULL 
  DROP FUNCTION dbo.fn_NextWeekDay
GO
CREATE FUNCTION dbo.fn_NextWeekDay(
     @aDate     DATE
   , @dayofweek NVARCHAR(30)
  )
RETURNS DATE
AS
/*
  SELECT dbo.fn_NextWeekDay('2016-12-14', 'fri')
  SELECT dbo.fn_NextWeekDay('2016-03-15', 'mon')
*/
BEGIN
  DECLARE @dx INT = 6
  WHILE UPPER(DATENAME(weekday,@aDate)) NOT LIKE UPPER(@dayofweek) + '%'
  BEGIN

    SET @aDate = DATEADD(day,1,@aDate)

    SET @dx=@dx-1
    if @dx < 0 
    BEGIN
      SET @aDate = NULL 
      BREAK
    END
  END

  RETURN @aDate

END
GO

这篇关于如何获取星期六的日期(或任何其他工作日的日期) - SQL Server的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

10-24 15:06