如何找到给定日期的最近(星期几) [英] How to find the Nearest (day of the week) for a given date

问题描述

我一直在练习查询，我目前的方案是查找给定日期最近的星期六.在我搞清楚逻辑之后，我想出了一个看起来像一个又长又乱的查询.我想知道是否有办法简化这一点.这是我的查询

I been practicing queries, and my current scenario is to find the nearest Saturday for a given date. After i got the logic down, i came up with a, whats looks like a long and messy query. And I was wondering if there is a way to simplify this. Here's my query

DECLARE @DATE DATE
SET @DATE ='2013-09-13'

IF              DATENAME(DW,@DATE) = 'SUNDAY'
    BEGIN 
        SELECT  DATEADD(DAY,-1,@DATE) AS DATE, 'IS THE NEAREST SATURDAY'
    END
ELSE IF         DATENAME(DW,@DATE) = 'MONDAY'
    BEGIN 
        SELECT  DATEADD(DAY,-2,@DATE) AS DATE, 'IS THE NEAREST SATURDAY'
    END
ELSE IF         DATENAME(DW,@DATE) = 'TUESDAY'
    BEGIN 
        SELECT  DATEADD(DAY,-3,@DATE) AS DATE, 'IS THE NEAREST SATURDAY'
    END
ELSE IF         DATENAME(DW,@DATE) = 'WEDNESDAY'
    BEGIN 
        SELECT  DATEADD(DAY,3,@DATE) AS DATE, 'IS THE NEAREST SATURDAY'
    END
ELSE IF         DATENAME(DW,@DATE) = 'THURSDAY'
    BEGIN 
        SELECT  DATEADD(DAY,2,@DATE) AS DATE, 'IS THE NEAREST SATURDAY'
    END
ELSE IF         DATENAME(DW,@DATE) = 'FRIDAY'
    BEGIN 
        SELECT  DATEADD(DAY,1,@DATE) AS DATE, 'IS THE NEAREST SATURDAY'
    END
ELSE IF         DATENAME(DW,@DATE) = 'SATURDAY'
    BEGIN
        SELECT  CONVERT(NVARCHAR,@DATE) + ' IS SATURDAY' AS DATE_DOW
    END

正如我们所看到的，查询很长并且运行多个 IF 来查找 TRUE 条件.请不要写查询，只是提示.我想自己处理查询.

As we can see the query is long and runs multiple IFs to look for TRUE condition. Please don't write the query, just hints. I would like to work on the query myself.

推荐答案

这是我的提示，没有回答，应您的要求:

Here's my hint without answering, as you requested:

考虑日期操作，使用当前日期(您可以使用 GETDATE() 或您的日期变量)、DATEADD() 和 DATEDIFF() 可以用几行来写.

Consider date manipulation, using current date (you can use GETDATE() or your date variable), DATEADD() and DATEDIFF() can be used to write this in a couple lines.

如果你只是想简化你的方法，你可以使用 CASE 语句:

If you just want to simplify your method you could use a CASE statement:

DECLARE @date DATE = '2013-09-13'
SELECT CASE WHEN DATENAME(DW,@DATE) = 'SUNDAY' THEN  CAST(DATEADD(DAY,-3,@DATE) AS VARCHAR(12)) + ' IS THE NEAREST SATURDAY'
            WHEN DATENAME(DW,@DATE) = 'MONDAY' THEN CAST(DATEADD(DAY,-2,@DATE) AS VARCHAR(12)) + ' IS THE NEAREST SATURDAY'
            WHEN DATENAME(DW,@DATE) = 'TUESDAY' THEN CAST(DATEADD(DAY,-1,@DATE) AS VARCHAR(12)) + ' IS THE NEAREST SATURDAY'
            WHEN DATENAME(DW,@DATE) = 'WEDNESDAY' THEN CAST(DATEADD(DAY,1,@DATE) AS VARCHAR(12)) + ' IS THE NEAREST SATURDAY'
            WHEN DATENAME(DW,@DATE) = 'THURSDAY' THEN CAST(DATEADD(DAY,2,@DATE) AS VARCHAR(12)) + ' IS THE NEAREST SATURDAY'
            WHEN DATENAME(DW,@DATE) = 'FRIDAY' THEN CAST(DATEADD(DAY,3,@DATE) AS VARCHAR(12)) + ' IS THE NEAREST SATURDAY'
            ELSE CONVERT(NVARCHAR,@DATE) + ' IS SATURDAY'
       END

为了澄清我暗示和 Sparky 发布的方法，您需要调整 DATEFIRST 以使其工作，它适用于一周的第一天，星期六是第 7 天星期几，所以:

To clarify on the method I was hinting at and Sparky posted, you need to adjust DATEFIRST to make this work, it works for whichever day is the first day of the week, Saturday is the 7th day of the week, so:

SET DATEFIRST 7
DECLARE @date DATE = '2013-09-21'
SELECT DATEADD(day,7-DATEPART(weekday,@date),@date)

这篇关于如何找到给定日期的最近(星期几)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！