在SQL Server中实现日期

Disclaimer: I am just looking for a logic not code

免责声明:我只是在寻找逻辑而不是代码

John discovered a strange island called Rasa. The years and weeks on the island are weird. Digging deeper into the island's calendar, he found out that it is similar to rest-of-the-world's (ROW) calendar but the island calendar's Year starts on 1st week of February's calendar. John is asking you to help him solve the problem of converting ROW's calendar into Island's calendar. Here is the question.

约翰发现了一个名叫拉莎的奇怪岛屿。岛上的岁月和周数都很奇怪。他深入了解岛上的日历,发现它类似于世界休息日(ROW)日历,但岛日历的年份从2月的第一周开始。约翰要求你帮助他解决将ROW日历转换成Island日历的问题。这是个问题。

You are given a date (today's date). You have to determine the Island week's number. The catch here is that the Island year starts from 1st week of February and every Island's week starts from Sunday and ends on a Saturday. Write a SQL statement in SQL Server to achieve this. Use SQL Server functions and devise a logic.

您将获得一个日期(今天的日期)。你必须确定岛周的数字。这里的特点是岛屿年份从2月的第一周开始,每个岛屿的一周从周日开始,到周六结束。在SQL Server中编写SQL语句来实现此目的。使用SQL Server函数并设计逻辑。

• Input parameter: Any date.

输入参数:任何日期。

• Output parameter: Week No in Rasa Calendar.

输出参数:Rasa日历中的周数。

Here is an example:

这是一个例子:

• Date: 5th May 2015 --

日期:2015年5月5日 -

• Week No in ROW Calendar:19

行日历:19

• Week No in Rasa's Calendar:14

  Rasa日历中的周数:14

• Date: Jan 1 2017:

  日期:2017年1月1日:

• Week No in ROW Calendar:1

行日历:1

• Week No in Rasa's Calendar:49

Rasa日历中的周数:49

My question: can this be achieved in SQL Server?

我的问题:这可以在SQL Server中实现吗?

My homework: I tried a couple of ways to solve the problem.

我的作业:我尝试了几种方法来解决问题。

Approach #1:

• Step 1: Calculate the total no of days between today and Feb 1.

第1步:计算今天和2月1日之间的总天数。

• Step 2: Divide it by 7 and add 1 to the result.

  第2步:将其除以7并将结果加1。

  Later found out that this approach will not work if Feb 1 is on any day other than Sunday.

  后来发现,如果2月1日是星期日以外的任何一天,这种方法将不起作用。

  Eg: 1st Feb is on Wednesday. 5th Feb will be on Monday

  例如:2月1日是星期三。 2月5日星期一

  So, 1st Feb is on Rasa's week 1, and 5th Feb is on Rasa's week no 2. According to my approach 1st and 5th feb are on week 1 which is incorrect.

  因此,2月1日是在Rasa的第1周,2月5日是在Rasa的第2周。根据我的方法,第1和第5个星期一是第1周,这是不正确的。

Approach #2:

I thought removing 5 weeks of Jan from ROW's calendar should work

我想从ROW的日历中删除Jan的5周应该有效

select 
    case 
       when f.RasaWeek = -4 then 48
       when f.RasaWeek = -3 then 49
       when f.RasaWeek = -2 then 50
       when f.RasaWeek = -1 then 51
       when f.RasaWeek = 0 then 52
       else 
          f.RasaWeek
    end as Rasa_week,
    f.year, f.month, f.date 
from 
    (select 
         datepart(wk, date) - 5 as RasaWeek, * 
     from 
         <datetable> 
     where 
         Year(date) in (2011, 2012, 2013, 2014, 2015, 2016, 2017, 2018)) as f

Info: I tested this on a <datetable> but this code will break if there is a 53rd week. Notice that I was not able to take care of the 53rd week.

信息:我在 上对此进行了测试,但如果有第53周,此代码将会中断。请注意,我无法照顾第53周。

Any inputs to solve this problem are welcome.

欢迎任何解决此问题的输入。

1 个解决方案

#1

With dates, it's almost always easier to make a calendar table and store the data you care about rather than trying to do anything beyond basic date arithmetic. Use SQL's strengths: storing and retrieving data.

使用日期,制作日历表并存储您关心的数据几乎总是更容易,而不是尝试做除基本日期算术之外的任何事情。使用SQL的优势:存储和检索数据。

In this case, what you care about are all of the first Sundays in February. If you store these dates in a table, the solution is:

在这种情况下,你关心的是2月份的所有第一个星期日。如果将这些日期存储在表中,解决方案是:

RETURN
SELECT TOP 1
  DATEDIFF(day,[date],@input_date) / 7
FROM IslandCalendarStartDates
WHERE [date] <= @input
ORDER BY [date] DESC

This way you don't need to worry about leap years or 53-weeks, or any of the edge cases. Just count the days from the most recent first Sunday in February and divide by 7. If you need to change the solution to accommodate a different start date, you only change the data, not the code.

这样您就不必担心闰年或53周,或任何边缘情况。只计算2月份最近一个星期日的天数除以7.如果需要更改解决方案以适应不同的开始日期,则只更改数据,而不是代码。